SQL: Two criteria on one field doesn't return any records

[Peter Stone]

Access 2003, XP pro

The following statement works
SELECT tblDest.DestID, tblNm.Nm, tjnDestGeo.GeoID
FROM tblNm INNER JOIN ((tblDest INNER JOIN tjnDestGeo ON tblDest.DestID =
tjnDestGeo.DestID) INNER JOIN tjnDestNm ON tblDest.DestID = tjnDestNm.DestID)
ON tblNm.NmID = tjnDestNm.NmID
WHERE (((tblNm.NmCatID)=1) AND ((tjnDestGeo.GeoID)=4))
ORDER BY tblNm.Nm;

But the following (with two criteria on GeoID (4 And 10) in the WHERE
clause) doesn't return any records.
SELECT tblDest.DestID, tblNm.Nm, tjnDestGeo.GeoID
FROM tblNm INNER JOIN ((tblDest INNER JOIN tjnDestGeo ON tblDest.DestID =
tjnDestGeo.DestID) INNER JOIN tjnDestNm ON tblDest.DestID = tjnDestNm.DestID)
ON tblNm.NmID = tjnDestNm.NmID
WHERE (((tblNm.NmCatID)=1) AND ((tjnDestGeo.GeoID)=4 And
(tjnDestGeo.GeoID)=10))
ORDER BY tblNm.Nm;

Any suggestions please.

Thank you

Peter

 

[KARL DEWEY]

Try this --
WHERE (((tblNm.NmCatID)=1) AND ((tjnDestGeo.GeoID)=4 OR
(tjnDestGeo.GeoID)=10))

 

[Peter Stone]

Thanks Karl. Sorry I should have been clearer.
I only want records that meet both criteria. This can occur because
tjnDestGeo is a join table.

 

[KARL DEWEY]

A single record cannot have tjnDestGeo.GeoID=4 and10 at the same time.
A record can have tblNm.NmCatID=1 AND tjnDestGeo.GeoID=4.

OR tblNm.NmCatID=1 AND tjnDestGeo.GeoID=10.

You can write it this way --
WHERE (tblNm.NmCatID=1 AND tjnDestGeo.GeoID=4) OR (tblNm.NmCatID=1 AND
tjnDestGeo.GeoID=10)

 

[Peter Stone]

I will explain more
I am using the SQL to display Destination names in a list box
tblDest contains Destinations (countries)
tblNm contains the destinations' Names
tblNmCat contains types of names which are stored in tblNm.NmCatID
in this case: 1= Common name
tblGeo contains geographic terms
in this case: 1= Sovereign State, 4= Mainland
tjnDestGeo joins the Destinations to the Geographic terms

In the list box, I am looking to display the common names of records from
tblDest that are Mainland Sovereign states.

That means unique tblDest records that are joined to two records in tjnDestGeo

This is some kind of And criteria.

I hope this is a little clearer.

Thanks

Peter

 

[KARL DEWEY]

Your further explaination did not help.

 

[Peter Stone]

You're very patient.

WHERE (tblNm.NmCatID=1 AND tjnDestGeo.GeoID=4) OR (tblNm.NmCatID=1 AND
tjnDestGeo.GeoID=10)

Using OR in the WHERE clause produces two records when both 1 and 4 are true
And 1 record when only one of 1 and 4 are true.

I only want the record set where both 1 and 4 are true.

 

[John W. Vinson]

I only want the record set where both 1 and 4 are true.

Please explain how one record can have a SINGLE field which simultaneously is
equal to 1 and to 4.

It cannot happen as you have stated. A field can have only one value.

 

[AccessVandal via AccessMonster.com]

If you want both, it should be like

WHERE (tblNm.NmCatID=1 AND tjnDestGeo.GeoID IN ( 4,10))

 

[John Spencer]

Can tjnDestGeo have more than one record for the a specific DestId that has a
GeoID equal to 4? If not, then you can probably use a subquery to solve your
problem.

This query will identify all DestID that have both 4 and 10 as a GeoID

SELECT DestID
FROM tjnDestGeo
WHERE tjnDestGeo.GeoID in (4,10)
GROUP BY DestID
HAVING Count(*) =2

SELECT tblDest.DestID
, tblNm.Nm
, tjnDestGeo.GeoID
FROM tblNm INNER JOIN ((tblDest INNER JOIN tjnDestGeo ON tblDest.DestID =
tjnDestGeo.DestID) INNER JOIN tjnDestNm ON tblDest.DestID = tjnDestNm.DestID)
ON tblNm.NmID = tjnDestNm.NmID
WHERE tblNm.NmCatID=1 AND
tblDest.DestID in
(
SELECT DestID
FROM tjnDestGeo
WHERE tjnDestGeo.GeoID in (4,10)
GROUP BY DestID
HAVING Count(*) =2)
ORDER BY tblNm.Nm;

If the combination of GeoID and DestID is not unique then the subquery is a
bit more complicated since it would look like:

(SELECT Temp.DestID
FROM (SELECT DISTINCT GeoId, DestID
FROM tjnDestGeo) as Temp
WHERE Temp.GeoID in (4,10)
GROUP BY Temp.DestID
HAVING Count(*) =2)


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

 

[Peter Stone]

Sorry I've been asleep at then at work.
With blind luck and some aid from the grid, I pieced this together thanks to
the replies from John Spencer and Access Vandal--and it worked.

In answer to John Vinson: I had never used In in a WHERE clause before; so,
even though I knew what I wanted, I was barking up the wrong tree. Now I know
how to do this.

John Spencer's suggestion had a syntax error in query expression Count (*) =
2 related to the middle select statement. But his first statement got me most
of the way.

Here's the statement that worked:

SELECT tblDest.DestID, tblNm.Nm, tblNm.NmCatID
FROM tblNm INNER JOIN ((tblDest INNER JOIN tjnDestGeo ON tblDest.DestID =
tjnDestGeo.DestID) INNER JOIN tjnDestNm ON tblDest.DestID = tjnDestNm.DestID)
ON tblNm.NmID = tjnDestNm.NmID
WHERE (((tjnDestGeo.GeoID) In (4,10)))
GROUP BY tblDest.DestID, tblNm.Nm, tblNm.NmCatID
HAVING (((tblNm.NmCatID)=1) AND ((Count(*))=2));

Thanks for the patience and help. I've tried asking this question before,
but I guess I wasn't able to express it properly. This enables me to move
forward, because I many queries in waiting that will use similar syntax.