Results 1 to 6 of 6
  1. #1
    Carbontrader is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    12

    Comparing two queries

    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:


    Click image for larger version. 

Name:	Image 1.JPG 
Views:	14 
Size:	72.6 KB 
ID:	35961 Click image for larger version. 

Name:	Image 2.JPG 
Views:	13 
Size:	62.6 KB 
ID:	35962

    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.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Use unmatched queries to do this. Access provides a wizard to get you started.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Carbontrader is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    12
    Thanks for this. I got it to sort-of work:

    qryMissingUniPhiHours:

    SELECT qrySAPWeeklyHours.ID, qrySAPWeeklyHours.UniPhiID, qrySAPWeeklyHours.FirstName, qrySAPWeeklyHours.LastName, qrySAPWeeklyHours.[Week Ending Date], qrySAPWeeklyHours.[SumOfTotal Hours]
    FROM qrySAPWeeklyHours LEFT JOIN qryUniPhiWeeklyHours ON qrySAPWeeklyHours.[Week Ending Date] = qryUniPhiWeeklyHours.WkEnding
    WHERE (((qryUniPhiWeeklyHours.WkEnding) Is Null));


    This query finds 4 records missing from qryUniPhiWeeklyHours, the four for which there are no entries for that week ending date. But there are actually 7 missing records, because 1 person did enter a UniPhi timesheet. I think the problem is that there are two key fields linking the queries, [EmployeeNo] plus [WkEnding], not one.

    Any hints as to how I would modify the query to accommodate that?

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Try this.
    Modify the query to include both fields.
    Use both in the join and in the WHERE clause as null.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    perhaps also modify your where clause to include

    qrySAPWeeklyHours.[SumOfTotal Hours]<>nz(qryUniPhiWeeklyHours.SumOfHours,0)

    to pick up differences in the hours

    you may also need to do a join the other way to find records in qryUniPhiWeeklyHours which are not in qrySAPWeeklyHours

    Also question your initial queries, you are using a right join which will return all records from tblRawData and tblUniPhiTimesheets and a null for tblStaff.ID if a record does not exist in tblStaff. Is this what you want?

  6. #6
    Carbontrader is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    12
    Thanks. Got it to work by creating a new field ("Key") in each select query that contains the concatenated values of the two key fields. Probably not the most elegant method but (not knowing SQL at all) easiest for me to understand.

    I also took your suggestion, Ajax, and changed the WHERE clause to:

    WHERE (((qrySAPWeeklyHours.[SumOfTotal Hours])<>Nz([qryUniPhiWeeklyHours].[SumOfHours],0))) OR (((qryUniPhiWeeklyHours.Key) Is Null));

    and it picks up all the incorrect hours. Thanks for that.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Comparing 2 set of dates.
    By Andy42k in forum Access
    Replies: 3
    Last Post: 04-18-2016, 12:40 PM
  2. Comparing two tables
    By JAYAPRAKASH in forum Access
    Replies: 0
    Last Post: 02-25-2015, 12:56 AM
  3. Comparing Date
    By J Guggs in forum Queries
    Replies: 1
    Last Post: 01-15-2012, 12:41 PM
  4. Comparing records using forms, reports or queries
    By jonathanjilla in forum Access
    Replies: 2
    Last Post: 12-10-2011, 10:48 AM
  5. Comparing Like Records
    By JSAKelley in forum Queries
    Replies: 0
    Last Post: 04-16-2011, 05:31 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums