how to return records not in a query

[spottednewt]

I have a query that returns a set of employee records. I'm trying to create another query that returns all employees NOT in the previous query. My problem is that the second query excludes just the first employee in the previous and not the rest. Can anyone help?

 

[PHV]

Take a look at the query wizards, one is about retrieving unmatched records.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MikeRBS]

This should be very easy. Post your SQL here and we can spot the problem.

 

[spottednewt]

Availability is my main report. The subreport that should show all employee id's (volno) not in the main report is based on the following SQL

SELECT [volunteers].[volno] FROM volunteers WHERE ((Not ([volunteers].[volno])=[Reports]![availability]![volno]));

If I put the subreport under detail of the main report, it works fine. But I don't want a list under every record. I want the subform list under the group footer. When I move the subform under the group footer, it just excludes the first detail record.

 

[SkipVought]

Hi,

Use ...
...
Where Not In (OriginalQueryListOfEmployees)



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

oops


SELECT [volunteers].[volno]
FROM volunteers
Where [volunteers].[volno] Not In (SELECT [volunteers].[volno] FROM volunteers WHERE ((Not ([volunteers].[volno])=[Reports]![availability]![volno])))


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[spottednewt]

That string is still just excluding the first record in the query...i'm stumped.

But thanks!