This has got to be dead easy, but I am baffled!
I manage an engineering business. When we do joint ventures, or work as a subconsultant, my staff complete weekly timesheets in two systems: ours (using SAP) and the JV's (using UniPhi). Every month at invoicing time, I have to ensure our billed hours match the JV's hours. So I get a timesheet data dump from each system and import into my Access database for that project. Using relatively simple select queries (with some totaling to get the daily hours into weekly hours), I process each of the raw data tables into matching datasets: qrySAPWeeklyHours and qryUniPhiWeeklyHours, respectively. They have identical record designs except the field names differ slightly:
The queries are as follows:
qrySAPWeeklyHours:
SELECT tblStaff.ID, tblStaff.UniPhiID, tblStaff.FirstName, tblStaff.LastName, tblRawData.[Week Ending Date], Sum(tblRawData.[Total Hours]) AS [SumOfTotal Hours]
FROM tblStaff RIGHT JOIN tblRawData ON tblStaff.ID = tblRawData.[Employee Number]
GROUP BY tblStaff.ID, tblStaff.UniPhiID, tblStaff.FirstName, tblStaff.LastName, tblRawData.[Week Ending Date]
HAVING (((tblStaff.ID)<>999999))
ORDER BY tblStaff.LastName, tblRawData.[Week Ending Date];
UniPhiWeeklyHours:
SELECT tblStaff.ID, tblUniPhiTimesheets.EmployeeNo, tblStaff.FirstName, tblStaff.LastName, WkEndingDate([Date]) AS WkEnding, Sum(tblUniPhiTimesheets.Hours) AS SumOfHours
FROM tblStaff RIGHT JOIN tblUniPhiTimesheets ON tblStaff.UniPhiID = tblUniPhiTimesheets.EmployeeNo
GROUP BY tblStaff.ID, tblUniPhiTimesheets.EmployeeNo, tblStaff.FirstName, tblStaff.LastName, WkEndingDate([Date])
ORDER BY tblStaff.LastName, WkEndingDate([Date]);
What I'm looking for is a list of missing records from the UniPhi dataset, or any records that have hours entered that are different. I would seem to me to be simple exercise, but I can't see how to do this. Obviously I can do this in Excel in about 3 1/2 minutes, but that's cheating.
Any assistance is greatly appreciated.