Query on Multiple Tables Question

[DavidWE]

I need to query three tables – Employee, ADPHours, and PayJournal. Each entry in ADPHours
and PayJournal will have an EmployeeID that will match an EmployeeID in the Employee table.
Some employees will have entries in both ADPHours and PayJournal. Some will have an entry
in only one of those two tables.

I would like my query to select a field called DailyHrs from the ADPHours table and ProcessPay
from the PayJournal table plus the employee information from the Employee table. Some of the
records created by the query will have values for both DailyHrs and ADPHours. Some will have an
entry in only one.

The query needs to match the tables on EmployeeID and date parameters passed from a form.

A simple query (what I consider a simple query) on all three tables will only create a record for an
EmployeeID if there is a matching EmployeeID and date in all three tables. I need to create a record
from the query even if there is only matching criteria in either the ADPHours or the PayJournal table.
There will always be a matching EmployeeID in the Employee table. I don't really need an
entry from the Employee table if there are no matching entries in the other two tables.

Can anyone give me any tips on writing the query? It would basically be a combination of the two
queries below.

PARAMETERS [Forms]![ExportPR].[txtStartDate] DateTime, 
[Forms]![ExportPR].[txtEndDate] DateTime;
SELECT [Employee].[EmployeeID], [Employee].[LastName],
[Employee].[FirstName], [Employee].[Rate], [Employee].[Department],
([ADPHours].[DailyHrs]) AS Hours, [ADPHours].[SDate]
FROM Employee, ADPHours
WHERE ((([ADPHours].[SDATE])>=[Forms]![ExportPR].[txtStartDate]
And ([ADPHours].[SDATE])<[Forms]![ExportPR].[txtEndDate]+1)
And ([ADPHours].[EMPNUM])=[Employee].[EmployeeID])
ORDER BY [Employee].[Department], [Employee].[LastName],
[Employee].[FirstName], [ADPHours].[SDATE];

PARAMETERS [Forms]![ExportPR].[txtStartDate] DateTime,
[Forms]![ExportPR].[txtEndDate] DateTime;

SELECT [Employee].[EmployeeID], [Employee].[LastName],
[Employee].[FirstName], [Employee].[Rate], [Employee].[Department],
([PayJournal].[ProcessPay) AS PieceRate
FROM Employee, PayJournal
WHERE ((([PayJournal].[ProcessDate])>=[Forms]![ExportPR].[txtStartDate]
And ([PayJournal].[ProcessDate])
<[Forms]![ExportPR].[txtEndDate]+1) And ([PayJournal].[EmpID])=
[Employee].[EmployeeID])
ORDER BY [Employee].[Department], [Employee].[LastName],
[Employee].[FirstName], [PayJournal].[ ProcessDate];

Thanks for any help.

 

[DCrake]

You need to create outer joins on both the Pay and ADP files against your employees table. In other words set it so it displays all records in the Employees table even if there is no record in the ADP and likewise for the Pay table. However you are going to get a cartesian effect because if you have a record in each file you will a two lines for 1 emplyee one for each table.

My question is why do employees have entries in both tables?

 

[DavidWE]

Thanks, DCrake

The ADPHours table contains hours we download from the company that does our payroll.
The PayJournal table contains entries that were scanned. These entries are processes the employees complete in our manufacturing plant. That table also stores the rates for each process. I have thought about creating one table from the two tables, but it would involve some duplication.

I was probably misguided in attempting to combine the two above queries. The two tables do not have the same number of fields, and the fields are different. I believe the tables would have to match for the outer joins to work.

Right now I am working on a program that will query both tables separately. The program would then combine them on the report by matching the EmployeeID and date.

Thanks for responding.