Returning records not matching criteria from second table

B

BJ Scout

I have one query that contains the following fields (among others):
Call #
Employee # (attending call)
The call information appears for each employee that attended; in other words
one call will show up 5 times if 5 people attended with the only difference
being the employee #.
I need to create a query that will list the call# and employee # for those
employees that did not attend. Example, there are 10 employees and I need to
see the 5 that did not attend. I have an table with the employee numbers
listed but can't figure out how to get the opposite information I am getting
now.
 
D

David Lloyd

One alternative solution is to create a table with all possible Call #,
Employee # combinations and then join this against your existing call list.
This can be done using the following three steps.

One, create a list of distinct call numbers with a dummy Employee number (-1
in this case). For example:

SELECT DISTINCT Calls.CallNum, -1 AS ENum
FROM Calls;

I named this query "UniqueCalls."

Second, create the master list of Call #, Employee #. For example:

SELECT UniqueCalls.CallNum, Employees.EmployeeNum
FROM Employees INNER JOIN UniqueCalls ON Employees.EmployeeNum <>
UniqueCalls.ENum;

Notice this is an unequal join and since I am assuming none of your
employees have an Employee # of -1, this will create the master list of all
Call/Employee combinations. I called this query MasterCallList

Third, join our master list with the existing Call table (query). I am
calling your existing calls list "Calls." For example:

SELECT MasterCallList.CallNum, MasterCallList.EmployeeNum
FROM Calls RIGHT JOIN MasterCallList ON (Calls.EmployeeNum =
MasterCallList.EmployeeNum) AND (Calls.CallNum = MasterCallList.CallNum)
WHERE (((Calls.CallNum) Is Null));

This third query uses an outer join to find those records in the
MasterCallList that do not exist in the Calls table (query).

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have one query that contains the following fields (among others):
Call #
Employee # (attending call)
The call information appears for each employee that attended; in other words
one call will show up 5 times if 5 people attended with the only difference
being the employee #.
I need to create a query that will list the call# and employee # for those
employees that did not attend. Example, there are 10 employees and I need
to
see the 5 that did not attend. I have an table with the employee numbers
listed but can't figure out how to get the opposite information I am getting
now.
 
B

BJ Scout

Ok, I think I'm confused. I have the following tables:
Employee by ID (Named: Personnel)
Call List with each employee responding (multiple instances of call #'s)
(Named: Calls)

Query Named (Incidents) created from Calls table:
Call List by distinct Call # (one instance for each call #) (Named Incident)

I import the info in Calls table from Excel - and I have to automate
everything from that point on. Can I make the "Unique Calls" table you
referred to with a make table query and if so how do I get a dummy employee
number to populte each field?
 
D

David Lloyd

You do not really need to create a table for the "Unique Calls" information.
In Access, a query can be referenced in the FROM clause of another query,
just as though it were a table. Therefore, you can just create a query
similar to the "UniqueCalls" query and then reference this query in the next
query.

Its sounds as though your "Incidents" query is similar to the "UniqueCalls"
query, although it may not have a dummy employee number field.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Ok, I think I'm confused. I have the following tables:
Employee by ID (Named: Personnel)
Call List with each employee responding (multiple instances of call #'s)
(Named: Calls)

Query Named (Incidents) created from Calls table:
Call List by distinct Call # (one instance for each call #) (Named Incident)

I import the info in Calls table from Excel - and I have to automate
everything from that point on. Can I make the "Unique Calls" table you
referred to with a make table query and if so how do I get a dummy employee
number to populte each field?
 
B

BJ Scout

THANK YOU SO MUCH! I finally got it - same theory as what you said but I
worked around it a little different. I had a field for Shifts (shift assigned
to) that was also in the call table so I was able to create a query with the
Employee table and the Call table and by using the shift field (from each
table) it created a "master list" showing every call with every Employee #. I
ended up with a separate list for each shift but there are only 3 and I
needed that anyway.
Thanks again.

<sometimes we just need a jolt to the brain>
 
Top