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:
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:
When I run the query, what I end up getting is duplicated data in many
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