Query to select from one table where no record in another table

[Don S.]

I have a database where I keep various information about employees. One
table "Employees" has their name and various other data.

In another table "Accidents" I keep up with data relative to accidents they
have had. This table has the date and other information about any accident
an employee has had.

I would like to recognize those employees who for a specified period of time
(calendar year) have not had any accidents.

I would appreciate your assistance in setting this up.

 

[akphidelt]

Theres probably easiar ways to do it, but im not a professional. What I would
do is create an accidents query. Create a field that puts the number 1 in
every row. Then go to view totals. Groups by name and sum by your dummy field
that has 1 in every row.

Then when you create a Join query with the names and accidents. Now whenever
an employee doesn't have an accident there will be a blank next to their
name. So in the query parameters, type in "Null"

That's probably 1 out of 1000 ways to do it... but that is the best I can
come up with.

 

[Darren Bartrup]

There is an easier way to do it.
On your database window select New Query and use the Find Unmatched Query
Wizard.
This will give you something like:

SELECT tblEmployeeDetails.fldEmployeeID, tblEmployeeDetails.fldEmployeeName
FROM tblEmployeeDetails LEFT JOIN tblAccidentTable ON
tblEmployeeDetails.fldEmployeeID = tblAccidentTable.fldEmployeeID
WHERE tblAccidentTable.fldEmployeeID Is Null;

Basically, bring back all the employee names from the employee details table
where a link between the employee id fields from both tables would be null.

 

[John Spencer]

Two query approach.

Query one: Get everyone that HAS HAD an Accident during the period you are
interested in. Save this query

Query two: Use the unmatched query wizard against your employees table and
query one.

IF you table and field names consist of only letters, numbers, and
underscore characters you can do this all in one query that would look like
the following. Note that you cannot build this query in the query grid
(design view) but must use the SQL view.

SELECT Employees.EmployeeLastName, Employees.EmployeeFirstName
FROM Employees LEFT JOIN
(SELECT DISTINCT EmpID
FROM Accidents
WHERE Accidents.DateField Between #2007-01-01# and #2007-06-30#) as A
On Employees.EmpID = A.EmpID

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..