Usual Filter

[Krzysztof via AccessMonster.com]

Good Morning to those on EDT!

I have a problem that I cannot seem to get. I have a set of complex queries,
and i am stumped on this particular part. Let me explain:

i have a result set:

ID | Name | Status

33 SITE3 2
33 SITE3 3
33 SITE3 0
44 SITE4 3
44 SITE4 3
44 SITE4 6


I have a SiteID, SiteName, and the status of the reading. i need to
formulate a list that shows the sites that at least one reading was 0. so
site 44 would not make the list. but i also need to show the other readings
as well, or all three records for site 33.

i am trying to filter is such a fashion, put I have been spinning my wheels
all weekend.

i would greatly appreciate another idea!!!

 

[Allen Browne]

Create a query that filters only the status zero records:
SELECT ID, [Name] FROM Table1 WHERE Status = 0;

Now use that query as a source 'table' in another query. The INNER JOIN
(presumably on ID), will limit the new query to records that were in the
first one.

It would also be possible to solve this with a subquery, but the INNER JOIN
would be more efficient. If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

 

[Krzysztof via AccessMonster.com]

Thanks Allen,
It took me a moment to see what you meant, but now I realize. it works
great!

You want to 'filter' your results by those records that were zero, but i was
thinking of only one aspect. by adding another source to my query, and using
an inner join, you are doing the same thing.

thanks for your help with my issue, and broadening my perspective.

Create a query that filters only the status zero records:
SELECT ID, [Name] FROM Table1 WHERE Status = 0;

Now use that query as a source 'table' in another query. The INNER JOIN
(presumably on ID), will limit the new query to records that were in the
first one.

It would also be possible to solve this with a subquery, but the INNER JOIN
would be more efficient. If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

Good Morning to those on EDT!

[quoted text clipped - 22 lines]

wheels
all weekend.