Filter data by exluding records retrieved by query

[jamessl]

This seems simple enough...but I don't know how to do it!

I have a query that selects records based on date criteria from a table called Bookings. The returned data can contain more than one record with the same Employee number (in the Employee Number field).

I want a query or form which lists all of the records in a different table (Employees (primary key Employee Number)) excluding those records with Employee numbers that are the same as those retrieved by the aforementioned query.

The Employee field in the two tables is linked in a One to Many relationship (Employees Table - One, Bookings - Many).

Any ideas?? Help appreciated!

 

[sko]

SELECT tblEmployees.EmployeeID, tblEmployees.FName, tblEmployees.LName
FROM tblEmployees LEFT JOIN qryBookings ON tblEmployees.EmployeeID = qryBookings.EmployeeID
WHERE (((qryBookings.EmployeeID) Is Null));

Paste this in the sql window of a new query, using your query and field names. If you still have trouble, post back. You can do this with the "Find Unmatched Query Wizard"

 

[Eupher]

I recently had a very similar problem, i.e. I needed to query a list of jobs (bookings) to determine who was already engaged on a certain date/time, then use that info to retrieve a list of members (employees) from a 2nd table not matching the results of the query. If I've judged your intent correctly, you may need to use the first query as a subquery in your query. Here's the SQL of the query which works for me, maybe you can customize it for your purposes:

PARAMETERS [Enter Job Starting Date and Time] DateTime, [Enter Job Ending Date and Time] DateTime;
SELECT [Members].[SSN], [Members].[LastName], [Members].[FirstName]
FROM Members LEFT JOIN Jobs ON [Members].[SSN]=[Jobs].[SSN]
GROUP BY [Members].[SSN], [Members].[LastName], [Members].[FirstName]
HAVING (((Members.SSN)
Not In (SELECT Jobs.SSN FROM Jobs WHERE (((Jobs.StartDateTime)<[Enter Job Starting Date and Time]) AND ((Jobs.EndDateTime)>[Enter Job Ending Date and Time])) OR (((Jobs.StartDateTime) Between [Enter Job Starting Date and Time] And [Enter Job Ending Date and Time])) OR (((Jobs.EndDateTime) Between [Enter Job Starting Date and Time] And [Enter Job Ending Date and Time])))));

Obviously this is a parameter query which asks for starting date/time and ending date/time as input. The section after &quot;Not In&quot; is the subquery which returns a list of SSN's of members already working during any date/time that intersects with the date/time input. The top part of the SQL statement returns the list of non-matching members and the &quot;GROUP BY&quot; part eliminates duplicate entries.

See this thread for more info:

thread701-317097

HTH...

Ken S.

 

[Eupher]

I looked at my query again, and I think this one is probably a bit more efficient:

PARAMETERS [Enter Job Starting Date and Time] DateTime, [Enter Job Ending Date and Time] DateTime;
SELECT DISTINCTROW [Members].[SSN], [Members].[LastName], [Members].[FirstName]
FROM Members LEFT JOIN Jobs ON [Members].[SSN]=[Jobs].[SSN]
WHERE (((Members.SSN)
Not In (SELECT Jobs.SSN FROM Jobs WHERE (((Jobs.StartDateTime)<[Enter Job Starting Date and Time]) AND ((Jobs.EndDateTime)>[Enter Job Ending Date and Time])) OR (((Jobs.StartDateTime) Between [Enter Job Starting Date and Time] And [Enter Job Ending Date and Time])) OR (((Jobs.EndDateTime) Between [Enter Job Starting Date and Time] And [Enter Job Ending Date and Time])))));[/color]

Uses DISTINCTROW instead of GROUP BY...

Regards,

Ken S.

 

[jamessl]

Thanks! - will give it a go today and let you know how I got on...

 

[jamessl]

In fact the unmatched query wizard was all I needed in this case. Doh! with a capital D! Thanks for your responses.

James