Query Comparing Two Tables

  • Thread starter Thread starter Charles Allen
  • Start date Start date
C

Charles Allen

I have two tables. One has the following columns:
Employee ID
Class
Pay Code

The second one has these columns:
Class
Pay Code

The employee should have every pay code assigned to the class.

For example, employee JONES is in class HOURLY. The class can have these pay
codes: HOURLY, OT, VACN.

However, JONES only has HOURLY.

Here's how the data looks:
CLASS PAYCODE
HOURLY HOURLY
HOURLY OT
HOURLY VACN

EMPID CLASS PAYCODE
JONES HOURLY HOURLY

I'm trying to create a query where I can see which employees do not have all
of the pay codes that a class has.

Thank you in advance for your help.
 
If an employee should have every paycode in a class then WHY are you
redundantly attempting to store the PayCode in the first table? It should not
be necessary.

You can get all the paycodes by joining the two tables as follows

SELECT A.[Employee ID], A.Class, B.Paycode
FROM [FirstTable] as A INNER JOIN [SecondTable] as B
ON A.Class = B.Class

So to find all the missing paycodes if you insist on recording the class and
paycode, you could use the above query and the table in an unmatched query.

Save the above query and then construct a query that looks like the following:

SELECT Q.[Employee ID], Q.Class, Q.Paycode
FROM SavedQuery as Q LEFT JOIN FirstTable as A
ON Q.[Employee ID] = A.[Employee ID]
AND Q.Class = A.Class
AND Q.Paycode = A.Paycode
WHERE A.[Employee ID] is Null

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
The database design is by a third party so I have no control over it. But,
there are business reasons why one company would have employees with pay
codes that do not match the class setup. In some situations, the class does
not have any pay code data and it's only set up at the employee level. In
this case, we do want to ensure that an employee has all of the pay codes the
class has.

Thank you
--
Charles Allen, MVP



John Spencer (MVP) said:
If an employee should have every paycode in a class then WHY are you
redundantly attempting to store the PayCode in the first table? It should not
be necessary.

You can get all the paycodes by joining the two tables as follows

SELECT A.[Employee ID], A.Class, B.Paycode
FROM [FirstTable] as A INNER JOIN [SecondTable] as B
ON A.Class = B.Class

So to find all the missing paycodes if you insist on recording the class and
paycode, you could use the above query and the table in an unmatched query.

Save the above query and then construct a query that looks like the following:

SELECT Q.[Employee ID], Q.Class, Q.Paycode
FROM SavedQuery as Q LEFT JOIN FirstTable as A
ON Q.[Employee ID] = A.[Employee ID]
AND Q.Class = A.Class
AND Q.Paycode = A.Paycode
WHERE A.[Employee ID] is Null

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Charles said:
I have two tables. One has the following columns:
Employee ID
Class
Pay Code

The second one has these columns:
Class
Pay Code

The employee should have every pay code assigned to the class.

For example, employee JONES is in class HOURLY. The class can have these pay
codes: HOURLY, OT, VACN.

However, JONES only has HOURLY.

Here's how the data looks:
CLASS PAYCODE
HOURLY HOURLY
HOURLY OT
HOURLY VACN

EMPID CLASS PAYCODE
JONES HOURLY HOURLY

I'm trying to create a query where I can see which employees do not have all
of the pay codes that a class has.

Thank you in advance for your help.
Click to expand...
Click to expand...
 
Back
Top