Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    MasterBlaster is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Dec 2018
    Posts
    5

    Question Access Query Producing Inaccurate Sums (When Using Criteria)

    Hi all,



    I've inherited a database from my predecessor in a relatively new job role, and there is a problem with it that I have no idea how to fix.

    The database is fundamentally a performance database. The problem I am experiencing is that when running a report with criteria (e.g. specifying date and shift), the night shift numbers are all completely wrong, despite the base data input being accurately. The day shift numbers are fine.

    For now, I'm just pulling the data out of Access tables and working in Excel, but this is not a solution as the database is accessed by a number of individuals who like to and are used to inputting and running reports from there (though nobody has noticed the issue in the past 4 years). For what its worth, the SQL for the query is below.

    Any ideas?

    Code:
    SELECT 
    Fiscal_calendar.Fiscal_Year, Fiscal_calendar.Quarter, Fiscal_calendar.Period, Fiscal_calendar.Week, 
    [00 Inbound productivity].ProdDate,
     [00 Inbound productivity].shift,
     [SumOfUnits for Broken case receiving]/[SumOfOffloading_Hrs] AS BrkCaseRec_UPH,
     [SumOfUnits for case receiving]/[SumOfHrs for case receiving] AS CaseRec_UPH,
     [SumOfUnits for Pallet level receiving]/[SumOfHrs for Pallet level receiving] AS PalRec_UPH,
     [SumOfTasks processed]/([SumOfHrs for case receiving]+[SumOfHrs for Pallet level receiving]) AS Rec_TasksPerHour,
     [SumOfBRK_Units_Putaway]/[SumOfBC_Put_Hours] AS BRKPA_UPH,
     [SumOfBRK_Ctns_Putaway]/[SumOfBC_Put_Hours] AS BRKPA_CPH,
     [SumOfBRK_Putaway_WTS]/[SumOfBC_Put_Hours] AS BRKPA_TasksPH, 
    [SumOfCST_units_putaway]/[SumOfCST_Put_Hours] AS CasePA_UPH, 
    [SumOfCST_Ctns_putaway]/[SumOfCST_Put_Hours] AS CasePA_CPH, 
    [SumOfCST_Putaway_WTS]/[SumOfCST_Put_Hours] AS CasePA_TaskPH, 
    [SumOfPST_units_Putaway]/[SumOfPST_Put_Hours] AS PalletPA_UPH, 
    [SumOfPST_Ctns_Putaway]/[SumOfPST_Put_Hours] AS PalletPA_CPH, 
    [SumOfPST_Putaway_WTS]/[SumOfPST_Put_Hours] AS PalletPA_TasksPH, 
    Sum([00 Inbound productivity].[Units for Broken case receiving]) AS [SumOfUnits for Broken case receiving], 
    Sum([00 Inbound productivity].[Units for case receiving]) AS [SumOfUnits for case receiving],
     Sum([00 Hours].[Hrs for case receiving]) AS [SumOfHrs for case receiving],
     Sum([00 Inbound productivity].[Units for Pallet level receiving]) AS [SumOfUnits for Pallet level receiving],
     Sum([00 Hours].[Hrs for Pallet level receiving]) AS [SumOfHrs for Pallet level receiving],
     Sum([00 Inbound productivity].[Tasks processed]) AS [SumOfTasks processed],
     Sum([00 Inbound productivity].BRK_Units_Putaway) AS SumOfBRK_Units_Putaway,
     Sum([00 Inbound productivity].BRK_Ctns_Putaway) AS SumOfBRK_Ctns_Putaway,
     Sum([00 Inbound productivity].BRK_Putaway_WTS) AS SumOfBRK_Putaway_WTS,
     Sum([00 Hours].BC_Put_Hours) AS SumOfBC_Put_Hours,
     Sum([00 Inbound productivity].CST_units_putaway) AS SumOfCST_units_putaway,
     Sum([00 Inbound productivity].CST_Ctns_putaway) AS SumOfCST_Ctns_putaway,
     Sum([00 Inbound productivity].CST_Putaway_WTS) AS SumOfCST_Putaway_WTS,
     Sum([00 Hours].CST_Put_Hours) AS SumOfCST_Put_Hours,
     Sum([00 Inbound productivity].PST_units_Putaway) AS SumOfPST_units_Putaway,
     Sum([00 Inbound productivity].PST_Ctns_Putaway) AS SumOfPST_Ctns_Putaway,
     Sum([00 Inbound productivity].PST_Putaway_WTS) AS SumOfPST_Putaway_WTS,
     Sum([00 Hours].PST_Put_Hours) AS SumOfPST_Put_Hours, 
    Sum([00 Inbound productivity].[Stock move cases]) AS [SumOfStock move cases], 
    Sum([00 Hours].[Stock Move Hours]) AS [SumOfStock Move Hours], 
    Sum([00 Inbound productivity].[Consolidation Tasks]) AS [SumOfConsolidation Tasks], 
    Sum([00 Hours].[Consolidation Hours]) AS [SumOfConsolidation Hours], 
    Sum([00 Hours].Offloading_Hrs) AS SumOfOffloading_Hrs, 
    Sum([00 Hours].Staging_Hrs) AS SumOfStaging_Hrs, 
    Sum([00 Hours].Redirect_Hrs) AS SumOfRedirect_Hrs, 
    Sum([00 Hours].Bargain_rec_Hrs) AS SumOfBargain_rec_Hrs, 
    Sum([00 Hours].[Meeting Hrs]) AS [SumOfMeeting Hrs], 
    Sum([00 Hours].[Training Hrs]) AS [SumOfTraining Hrs], 
    Sum([00 Hours].[Problem solver Hrs]) AS [SumOfProblem solver Hrs], 
    Sum([00 Hours].[Hourly Team leads Hrs]) AS [SumOfHourly Team leads Hrs], 
    Sum([00 Hours].[Clerical Hrs]) AS [SumOfClerical Hrs], 
    Sum([00 Hours].[Sick Hours]) AS [SumOfSick Hours], 
    Sum([00 Hours].[Vacation Hrs]) AS [SumOfVacation Hrs], 
    Sum([00 Hours].[Loan out Hrs]) AS [SumOfLoan out Hrs],
     Sum([00 Hours].[Borrowed Hrs]) AS [SumOfBorrowed Hrs],
     [SumOfHrs for case receiving]+[SumOfHrs for Pallet level receiving]+[SumOfBC_Put_Hours]+[SumOfCST_Put_Hours]+[SumOfPST_Put_Hours]+[SumOfOffloading_Hrs]+[SumOfStaging_Hrs]+[SumOfRedirect_Hrs]+[SumOfBargain_rec_Hrs]+[SumOfConsolidation Hours]+[SumOfStock Move Hours] AS [Direct Hours],
     [SumOfMeeting Hrs]+[SumOfTraining Hrs]+[SumOfHourly Team leads Hrs]+[SumOfClerical Hrs]+[SumOfProblem solver Hrs] AS [Indirect Hours],
     Sum([00 Hours].[Birthday hours]) AS [SumOfBirthday hours],
     Sum([00 Hours].[DC Day Hours]) AS [SumOfDC Day Hours],
     Sum([00 Hours].[Bereavement hours]) AS [SumOfBereavement hours],
     Sum([00 Hours].[Jury duty Hours]) AS [SumOfJury duty Hours],
     Sum([00 Hours].[Unpaid time off Hours]) AS [SumOfUnpaid time off Hours],
     [SumOfConsolidation Tasks]/[SumOfConsolidation Hours] AS [Consolidation TPH],
     [SumOfStock move cases]/[SumOfStock Move Hours] AS [Movement TPH]
    
    FROM (Fiscal_calendar INNER JOIN [00 Hours] ON Fiscal_calendar.Date = [00 Hours].ProdDate) 
    LEFT JOIN [00 Inbound productivity] ON ([00 Hours].userID = [00 Inbound productivity].userID) AND ([00 Hours].shift = [00 Inbound productivity].shift) AND ([00 Hours].ProdDate = [00 Inbound productivity].ProdDate)
    GROUP BY Fiscal_calendar.Fiscal_Year, Fiscal_calendar.Quarter, Fiscal_calendar.Period, Fiscal_calendar.Week, [00 Inbound productivity].ProdDate, [00 Inbound productivity].shift
    HAVING ((([00 Inbound productivity].ProdDate)=[Input Date]) AND (([00 Inbound productivity].shift)=[Input Shift]))
    ORDER BY Fiscal_calendar.Fiscal_Year DESC , Fiscal_calendar.Quarter DESC , Fiscal_calendar.Period DESC , Fiscal_calendar.Week DESC , [00 Inbound productivity].ProdDate DESC , [00 Inbound productivity].shift DESC;

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,818
    So night shift is probably crossing midnight into the next day. This can cause complication. But that is too much SQL for me to read through and try to follow. Would really need data to work with.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I suspect this is a problem that I've dealt with before and my guess is that
    a) night shift crosses over midnight (as already mentioned)
    b) date/time fields are formatted as mm/dd/yyyy hh:mm:ss and
    c) your query does not account for the fact that the default time for input dates is 00:00:00
    d) you are querying by shift

    If the above is true, the data for last night's night shift will be cut off at midnight, thus everything that happens between then and end of shift (say 06:00 A.M.) is not being captured. If you need to query by shift, there are a few things you can do:
    - tell db users to input an end date that follows the start of the last shift being queried - not my favorite solution as it depends on consistent and correct user input. The end date pertaining to last night's shift becomes the date of the day following, not the date that the last shift started.
    - use DateAdd function to add either the number of minutes after midnight or the number of seconds. If the shift start is 11 pm and it ends 8 hours later, an input date becomes DateAdd("nn",25200,TheDate) where TheDate is however you're providing it now and 25200 is the number of seconds in 7 hours (I think).
    - you can add the end time to the end date such as: TheDate + #08:00:00# but again, that date would have to be the date that the shift ends, not starts and it's not clear what your present practice is.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  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
    I've also not attempted to follow that lengthy SQL

    Do you have a table tblShift with e.g. ShiftID, StartTime & EndTime.
    This will make it simple to correctly identify the ShiftID in your lengthy query, including shifts that traverse midnight
    The date (when the shift starts or ends but be consistent) together with ShiftID, possibly concatenated, will provide a unique identifier
    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
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As others have said, I also have not looked into the lengthy SQL. I think you should advise management of what you have found.
    And since you are new to the job and the discovery take your time to develop a solution in conjunction with management.

    As a debugging approach, I would mock up a few sample records --independent of your working database --and work with some simple queries to Sum hours where there is a crossover at midnight. I would work to get an algorithm to get a correct and consistent sum, and then take what I had learned into a copy of the working database to ensure the "logic/technique" is correct.
    Then, lay out a plan to implement same into your working database--BACKUP THAT database for safe keeping.
    How you will deal with the errors in sums collected/reported previously is an issue for your management. Don't assume anything, let management decide the "policy".

    Good luck.

  6. #6
    MasterBlaster is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Dec 2018
    Posts
    5
    Hi all,

    Many thanks for the responses thus far. Replies to key points:

    1. Data is uploaded in bulk at the end of the shift. The date field is just the date, there is no time element. Even if there was, the time would all be the same. Queries produce accurate sums without criteria.
    2. Unfortunately, I am management!
    3. The bulk of the SQL is just sums and expressions using summed fields. The following snippet is what I am unfamiliar with. I can work on providing a sample database if this is still insufficient:

    Code:
    FROM (Fiscal_calendar INNER JOIN [00 Hours] ON Fiscal_calendar.Date = [00 Hours].ProdDate) 
    LEFT JOIN [00 Inbound productivity] ON ([00 Hours].userID = [00 Inbound productivity].userID) AND ([00 Hours].shift = [00 Inbound productivity].shift) AND ([00 Hours].ProdDate = [00 Inbound productivity].ProdDate)
    GROUP BY Fiscal_calendar.Fiscal_Year, Fiscal_calendar.Quarter, Fiscal_calendar.Period, Fiscal_calendar.Week, [00 Inbound productivity].ProdDate, [00 Inbound productivity].shift
    HAVING ((([00 Inbound productivity].ProdDate)=[Input Date]) AND (([00 Inbound productivity].shift)=[Input Shift]))
    ORDER BY Fiscal_calendar.Fiscal_Year DESC , Fiscal_calendar.Quarter DESC , Fiscal_calendar.Period DESC , Fiscal_calendar.Week DESC , [00 Inbound productivity].ProdDate DESC , [00 Inbound productivity].shift DESC;
    4. There is no shift table. The database is fundamentally inefficient. This query uses a Fiscal Calendar table (FY, Period, Week, Date), Hours table & Production table. Both the hours and production share similar fields (Date, userID, username, shift) as you can see in the above snippet. I wonder if the joins, relationships or inefficient database architecture are contributing to screwy results. I would need a fine expert such as yourselves to explain what exactly the issue is, though.

    Apologies if I haven't been clear enough.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Unfortunately, I am management!
    Suggest you have a strong talk with yourself in that case

    Then create a shift table similar to the way I suggested
    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

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe you should step back and review the base data without all the calculations as the calculations themselves could be the culprit. I too could not delve into that mind bending sql.

  9. #9
    MasterBlaster is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Dec 2018
    Posts
    5
    Quote Originally Posted by ridders52 View Post
    Suggest you have a strong talkwith yourself in that case
    Every morning when I open my eyes!
    Quote Originally Posted by Micron View Post
    Maybe you should step back andreview the base data without all the calculations as the calculationsthemselves could be the culprit. I too could not delve into that mind bendingsql.

    As stated, queries without criteria sum correctly.
    OK, if the snippet is still too complicated then I shallprepare a sample database as advised. I will isolate the example I haveprovided to keep it simple. I would appreciate if someone could have a play andsee what they see.
    Please bear with me.

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    As it works without filter criteria it really has to be issues with determining the shift hours
    Before spending time uploading a sample db, do try creating a 'shift times' table and incorporating that into your data to ensure each shift is correctly counted and for the correct day
    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

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not suggesting it is causing the problem but can cause problems - you are breaking two naming convention rules. The first is to start table and field names with numbers, the second is having spaces. As access has matured, it have become less tolerant of this sort of thing. Strongly recommend you rename. A side benefit is that it would also make your query much easier to read.

    Other thoughts - your treatment of dates. Regardless of how they are formatted, in SQL they have to be presented in US format (mm/dd/yyyy) or yyyy-mm-dd. So with regards this part of your query

    HAVING ((([00 Inbound productivity].ProdDate)=[Input Date])

    if you are entering a date in the format dd/mm/yyyy then today (5/12/2018) will be treated as 12th May, not 5th Dec. If you are in the US then it is not normally a problem - just for the parts of the world that don't use mm/dd/yyyy.

    If this is the issue then try

    HAVING ((([00 Inbound productivity].ProdDate)=format([Input Date],"mm/dd/yyyy"))

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Using the Format function will change the date to a text string.
    Allen Browne has a function to do the "conversion" at http://www.allenbrowne.com/ser-30.html . Look for the heading "Dates in Strings".

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Using the Format function will change the date to a text string.
    ah yes, forgot the #'s

    HAVING ((([00 Inbound productivity].ProdDate)="#" & format([Input Date],"mm/dd/yyyy") & "#")

  14. #14
    MasterBlaster is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Dec 2018
    Posts
    5
    Again, thank you all for your input.

    Whilst creating the sample database, the solution was stumbled upon.

    Whilst changing user IDs and names for privacy purposes, it was discovered that there were spelling differences and missing names between the hours and production tables. As these fields were joined in the query, not all records could be matched with one another and it resulted in incorrect calculations.

    This is another fundamental flaw with the database design, as these fields should ideally be lookup fields from a separate employee table. Coupled with Ajax's point, it's clear the database needs rebuilding from the tables up.

    Thank you all again for your assistance.

  15. #15
    MasterBlaster is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Dec 2018
    Posts
    5
    Quote Originally Posted by Ajax View Post

    Other thoughts - your treatment of dates. Regardless of how they are formatted, in SQL they have to be presented in US format (mm/dd/yyyy) or yyyy-mm-dd. So with regards this part of your query

    HAVING ((([00 Inbound productivity].ProdDate)=[Input Date])

    This is fascinating. Different users had different date formats on their PC's due to the different countries they had came from. The query accepted the date format input by the user as long as it was consistent with the PC date format, it was not actually an issue! That's clever, no?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. DateDiff returning inaccurate result?
    By yeah in forum Programming
    Replies: 2
    Last Post: 04-20-2018, 11:43 AM
  2. Replies: 27
    Last Post: 08-19-2015, 07:14 AM
  3. Replies: 11
    Last Post: 11-11-2014, 08:59 AM
  4. Query sums based on checkbox criteria?????
    By mrmims in forum Queries
    Replies: 2
    Last Post: 07-06-2014, 02:42 PM
  5. Replies: 1
    Last Post: 03-23-2012, 09:45 AM

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