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;