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

[datadyl]

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?

 

[Marshall Barton]

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?

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

 

[datadyl]

Is there a way to do what you suggested through the Design View? I'm rather
new at this.

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?

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

 

[datadyl]

I just tried what you suggested and still came up with duplicate data,
however this time, I received all "Yes" values even for state and county
combinations that were NOT assigned to this builder. See below.


Bldr Job_ST Job_CO Assigned_CO_Shipment
2265 43 82 Yes
2265 43 82 Yes
2265 43 82 Yes
2265 43 82 Yes
2265 43 82 Yes
2265 43 82 Yes
2265 43 82 Yes
2265 43 82 Yes
2265 43 82 Yes
2265 43 82 Yes
2265 43 87 Yes
2265 47 20 Yes
2265 47 20 Yes
2265 47 35 Yes

In this example, for Bldr=2265, their only assigned state and county
combinations are:

Assigned_ST=43 and Assigned_CO=46
Assigned_ST=43 and Assigned_CO=82

So I should be getting "Yes" for all records below EXCEPT for the last three
that are to state and county combinations that are NOT assigned to this
builder.

Any ideas?

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?

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

 

[John Spencer]

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

 

[Marshall Barton]

I just tried what you suggested and still came up with duplicate data,
however this time, I received all "Yes" values even for state and county
combinations that were NOT assigned to this builder. See below.


Bldr Job_ST Job_CO Assigned_CO_Shipment
2265 43 82 Yes
2265 43 82 Yes
2265 43 82 Yes
2265 43 82 Yes
2265 43 82 Yes
2265 43 82 Yes
2265 43 82 Yes
2265 43 82 Yes
2265 43 82 Yes
2265 43 82 Yes
2265 43 87 Yes
2265 47 20 Yes
2265 47 20 Yes
2265 47 35 Yes

In this example, for Bldr=2265, their only assigned state and county
combinations are:

Assigned_ST=43 and Assigned_CO=46
Assigned_ST=43 and Assigned_CO=82

So I should be getting "Yes" for all records below EXCEPT for the last three
that are to state and county combinations that are NOT assigned to this
builder.

I don't see(?) anything wrong with my suggested query.
Please post a Copy/Paste of the query you used.

 

[John Spencer]

Marshall,

I believe you need to join on one more field bldr to get the desired result.

I posted that earlier, but have not heard back if my posting solved the
problem.

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

 

[Marshall Barton]

I believe you need to join on one more field bldr to get the desired result.

I posted that earlier, but have not heard back if my posting solved the
problem.

Thanks for pointing that out, John. Apparently I have a
blind spot about bldrs because I just didn't see it in your
reply either ;-)