At least I think of it as an advanced suggestion. I have a fairly good size database (11 Meg) that we have used for a few years to track tons and hours of production. I have a report that we use quite a bit to review said production. This query pulls data from a lot of tables. Here is the query:
My problem is that I need to add a total to the bottom of one of the report columns, but when I do I get a garbage number. This is because the record that has the hours for that day for a worker (qryTruckingDtl.[Total Hours]) related to 5-10 records of tons for the same worker that day (so there ends up being 5-10 instances of the Total Hours field displayed in the query). So the sum ends up being 5-10 times higher for each worker all included in the sum.
Is there a way to sum the correct number without running a subquery or something like that? I already have so many queries and reports in this database that its getting hard to keep track of. How do more advanced databases deal with these problems?
Thank you for any help or discussion on this!
Code:
SELECT tblEstList.EstNum, tblEstList.EstName, qryTruckingDtl.Date, tblPhaseItem.PhDesc, qryTruckingDtl.PhaseID, qryTruckingDtl.Drivers, qryTruckingDtl.DriverID, qryTruckingDtl.Trucks, qryTruckingDtl.TruckID, qryTruckingDtl.[Hourly Rate], qryTruckingDtl.[Total Hours], qryTruckingDtl.TruckDtlID, qryTruckingDtl.TruckOrderID, qryTruckingDtl.TicketNum, qryTruckingDtl.TicketQty, qryTruckingDtl.PlantArrive, qryTruckingDtl.PlantLeave, qryTruckingDtl.SiteArrive, qryTruckingDtl.SiteLeave, qryTruckingDtl.ReturnTime, qryTruckingDtl.[Load Time], qryTruckingDtl.[To Time], qryTruckingDtl.[Dump Time], qryTruckingDtl.[Back Time], qryTruckingDtl.Total, tblPhaseDtl.CycleTime, tblPhaseDtl.CPT, tblPhaseDtl.THH, [TicketQty]*[CPT] AS TotalRev
FROM (tblEstList INNER JOIN (tblPhaseItem INNER JOIN qryTruckingDtl ON tblPhaseItem.PhaseID = qryTruckingDtl.PhaseID) ON tblEstList.EstID = tblPhaseItem.EstID) LEFT JOIN tblPhaseDtl ON tblPhaseItem.PhaseID = tblPhaseDtl.PhaseID
WHERE (((tblEstList.EstNum) Like Nz([Forms]![frmEfficiencyReportInput]![EstNum],"*")) AND ((tblEstList.EstName) Like Nz([Forms]![frmEfficiencyReportInput]![EstName],"*")) AND ((qryTruckingDtl.Date) Between Nz([Forms]![frmEfficiencyReportInput]![StartDate],#1/1/1900#) And Nz([Forms]![frmEfficiencyReportInput]![EndDate],#12/31/9999#)) AND ((tblPhaseItem.PhDesc) Like Nz([Forms]![frmEfficiencyReportInput]![PhaseDesc],"*")) AND ((qryTruckingDtl.DriverID) Like Nz([Forms]![frmEfficiencyReportInput]![Driver],"*")) AND ((qryTruckingDtl.TruckID) Like Nz([Forms]![frmEfficiencyReportInput]![Truck],"*")));My problem is that I need to add a total to the bottom of one of the report columns, but when I do I get a garbage number. This is because the record that has the hours for that day for a worker (qryTruckingDtl.[Total Hours]) related to 5-10 records of tons for the same worker that day (so there ends up being 5-10 instances of the Total Hours field displayed in the query). So the sum ends up being 5-10 times higher for each worker all included in the sum.
Is there a way to sum the correct number without running a subquery or something like that? I already have so many queries and reports in this database that its getting hard to keep track of. How do more advanced databases deal with these problems?
Thank you for any help or discussion on this!
))
