Query criteria from a previous query

  • Thread starter Thread starter Ryan
  • Start date Start date
R

Ryan

Okay, so I'm just at a dead end.
I've made up a list of contactings with various people and one company could
end up having several records created (phone ins, phone out, faxes, etc...)
and when I complete with them and they sign up I click the box for sign up.
Now I can easily filter for those who have signed up - what I want is a
filter for those who haven't signed up.
Problem is that if I call Microsoft a few times I'll have 4 records of
Microsoft, only one of them is 'signed up', so the opposite filter pulls in
Microsoft the other 3 times.

-
Can I make a query criteria that says "if their company name is in the list
of 'signed up' company names DON'T display"
-
seems simple - but I'm wracking my brain!
 
Hi Ryan,
if you select the fields in the query carefully and include only the
relevant ones, you can use group by or distinct with your query.

If your query had the company id, company name and the field for complete,
you could go to the query's property dialog and set the Unique Values to yes
( to do this, first click in the big space above the query grid).

Alternatively you can right click in the query grid and choose Totals.

You may have to juggle with which fields you leave out of the query.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
First make a query that list those that are 'Signed Up.'
Then create a second query by placing your table and the 'Signed Up' query
in the area above the grid of design view.
Click on the common field of your table and drag to the corresponding field
of the 'Signed Up' query. Double click the connecting line and select the
option to view all of the records in the table and only those of the query
that match. Drag down the fields of the table you want to the Field row of
the grid.
Drag now the corresponding field of the 'Signed Up' query. Under this field
in the Criteria row enter Is Null and save.
Run query.
 
Back
Top