Return "yes" or "no" if value in one field matches any value in ot

Return "yes" or "no" if value in one field matches any value in ot

Post by ZGF0YWR5b » Wed, 15 Oct 2008 00:48:02


I have two tables: tbl_shipments and tbl_bldr_assigned_counties

I am trying to create a query that returns a "Yes" or "No" depending on
whether a particular job was shipped to a builder's assigned county.

Let's say Builder A shipped a job to State=1 and County=3. And let's say
that Builder A has the following state and county assignments:

Assigned_ST Assigned_CO
1 2
1 3
1 4

If I link both tables by "Builder" where it selects all the records in
tbl_shipments and only those records from tbl_bldr_assigned_counties where
the the joined field ("Builder") is equal.

Then I have a new field with the following definition:

Assigned_CO_Shipment:
IIf([tbl_shipments]![State]=[tbl_bldr_assigned_counties]![Assigned_ST] And
([tbl_shipments]![County]) In
([tbl_bldr_assigned_counties]![Assigned_CO]),"Yes","No")

When I run the query, what I end up getting is duplicated data in many
instances.

If Builder A shipped a job to State=1 and County=3, I get a "Yes" where this
state and county number combination matches Builder A's assigned state and
county but I get two more records with a "No" for Builder A's assigned state
and county combinations that do not match the job.

Job_No Assigned_ST Assigned_CO Assigned_CO_Shipment
0810 1 2 No
0810 1 3 Yes
0810 1 4 No

What am I doing wrong? How do you write an expression that gives a "Yes" if
a value matches any of the values in another table provided that the builders
are equal?
 
 
 

Return "yes" or "no" if value in one field matches any value in ot

Post by Marshall B » Wed, 15 Oct 2008 01:30:20


I think you need to use an outer join on both fields.

SELECT S.JobNo, S.State, S.County,
IIf(C.Assigned_CO Is Null, "No", "Yes") As
Assigned_CO_Shipment
FROM tbl_shipments As S
LEFT JOIN tbl_bldr_assigned_counties As C
ON S.State = C.Assigned_ST
AND S.County = C.Assigned_CO

--
Marsh
MVP [MS Access]

 
 
 

Return "yes" or "no" if value in one field matches any value in ot

Post by John Spenc » Wed, 15 Oct 2008 02:59:55

How about using an exists clause.

Field: AssignedCounty: Exists(SELECT * FROM tbl_bldr_Assigned_Counties as C
WHERE C.Assigned_ST = tbl_shipments.State and
C.Assigned_Co=tbl_shipments.County AND C.BuilderID = tbl_shipments.Builder)

In the query grid add the above expression to field

Another possibility would be to add both tables to the query
--Drag from state to state, county to county, and builder to builder
to set up the joins
--Now click on each join line and select Show All from tbl_Shipments and ONLY
from tbl_bldr_Assigned_Counties
--Next put the following in a field box.
Field: Assigned: [tbl_bldr_Assigned_Counties] is Not Null


Marshall's idea was correct except he forgot to include the BuilderID in the join.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County