Query results not summing correctly

[oxicottin]

Ok I have a question and have been trying to figure out how to do this query for days but come up with incorrect results every time. The reason I come up with the wrong results is because when I open my (frm_ShiftDay) it has a subform (frm_ShiftMachinesSubform) which on that subform I enter the employees name (EmployeeID) and how may hours worked (HrsWorked) and a few other bits of data then on that there is a subform (frm_MachineOutputSubform) which I would enter what
products (ProductID) and is net that it produced (NetProduct) for that employee which ran in the hours worked. Now here is where the query is calculating incorectly, Lets say Mitch worked 10hrs and had three productsID or products he ran then if I ran a query to sum (HrsWorked) for mitch it would say he worked 30hrs which it should
be only 10 hours he ran 3 product.


I have a sample DB from my original that I added some information in and here is the criteria:

Date start: 1/1/14
Date End: 4/6/14
Employee: 24

What I wanted it to do was give me the product name, How many times it was ran, sum of NetProduct for each product,
and production average (Which this is the issue).

With the criteria above the results should be for the [highlight #FCE94F]TEST query I made:[/highlight]

Product: B4 NB 5/8"    CountOfProduct: 3   SumOfNetProduct: 55000   ProductionAverage: 44897.959
Product: B2            CountOfProduct: 3   SumOfNetProduct: 75200   ProductionAverage: 39064.935
Product: B1 Superwide  CountOfProduct: 4   SumOfNetProduct: 161860  ProductionAverage: 83218.508

SAMPLE DB DIRECT DL FROM MY ONEDRIVE

Thanks,
SoggyCashew.....

 

[dhookom]

Maybe divide the average by the countofproduct?

Duane
Hook'D on Access
MS Access MVP

 

[oxicottin]

No that didnt work, Here is another example. If I broke it down to one product "B1 Superwide" to be easier and got rid of the Production Average and just wanted to show a Group By of HrsWorked and ran the query using:

SELECT tbluProducts.Product, Count(tbluProducts.Product) AS CountOfProduct, Sum(tblMachineOutput.NetProduct) AS SumOfNetProduct, tblShiftMachinesRan.HrsWorked
FROM tbluProducts INNER JOIN (tbluEmployees INNER JOIN ((tblShiftDay INNER JOIN tblShiftMachinesRan ON tblShiftDay.ShiftDayID = tblShiftMachinesRan.ShiftDayID) INNER JOIN tblMachineOutput ON tblShiftMachinesRan.ShiftMachineID = tblMachineOutput.ShiftMachineID) ON tbluEmployees.EmployeeID = tblShiftMachinesRan.EmployeeID) ON tbluProducts.ProductID = tblMachineOutput.ProductID
WHERE (((tblShiftDay.ShiftDate) Between [Forms]![frmSwitchboard].[txtStartDate] And [Forms]![frmSwitchboard].[txtEndDate]))
GROUP BY tbluProducts.Product, tblShiftMachinesRan.EmployeeID, tblShiftMachinesRan.HrsWorked
HAVING (((tbluProducts.Product)="B1 Superwide") AND ((tblShiftMachinesRan.EmployeeID) Like [Forms].[frmSwitchboard].[SubformTreeMenu].[Form].[cboEmployees] & "*"))
ORDER BY Sum(tblMachineOutput.NetProduct);

Date start: 1/1/14
Date End: 4/6/14
Employee: 24

The results show I ran it 4 times and the reason it shows it in two differnt rows is I ran it twice on one day and twice on another day.

OK, now I change that TEST query to SUM HrsWorked insted of Group By and this is where it counts each record.

SELECT tbluProducts.Product, Count(tbluProducts.Product) AS CountOfProduct, Sum(tblMachineOutput.NetProduct) AS SumOfNetProduct, Sum(tblShiftMachinesRan.HrsWorked) AS SumOfHrsWorked
FROM tbluProducts INNER JOIN (tbluEmployees INNER JOIN ((tblShiftDay INNER JOIN tblShiftMachinesRan ON tblShiftDay.ShiftDayID = tblShiftMachinesRan.ShiftDayID) INNER JOIN tblMachineOutput ON tblShiftMachinesRan.ShiftMachineID = tblMachineOutput.ShiftMachineID) ON tbluEmployees.EmployeeID = tblShiftMachinesRan.EmployeeID) ON tbluProducts.ProductID = tblMachineOutput.ProductID
WHERE (((tblShiftDay.ShiftDate) Between [Forms]![frmSwitchboard].[txtStartDate] And [Forms]![frmSwitchboard].[txtEndDate]))
GROUP BY tbluProducts.Product, tblShiftMachinesRan.EmployeeID
HAVING (((tbluProducts.Product)="B1 Superwide") AND ((tblShiftMachinesRan.EmployeeID) Like [Forms].[frmSwitchboard].[SubformTreeMenu].[Form].[cboEmployees] & "*"))
ORDER BY Sum(tblMachineOutput.NetProduct);

RESULTS: using same criteria.... Notice now everything is on one line (Which is what I want) BUT the hrsWorked is now 38.9. What it did was I ran the product twice one day for 10 hours and twice another day for 9.45 so it totaled like 10+10+9.45+9.45 = 38.9

Thanks,
SoggyCashew.....

 

[MajP]

I see the issue, and see how to band aid it. But I think the problem is the table structure. I cannot understand some of the relationships.

The table shiftMachineRan
contains machineID, employeeid, and shiftID
along with employee hours worked, and run minutes
Not sure how employee hours worked relates here, because that seems like a total and no relation to run time. In words what are these relations? This creates an artificial relationship where each record in a product output relates to the total employee hours worked.

Because I think there is an awkward relation, there is a problem here in the machine output table. If you relate the two tables you are going to get 4 records. So you need to use the distinct shiftID for each product.

ProductID ShiftMachineID
12 236
12 236
12 243
12 243

My band aid to get the correct hours is to first create a query that returns the Distinct EmployeeID, shiftid. Some smart SQL person (which I am not) can provide a better solution, but I still think there is some problem with the table structure.

SELECT DISTINCT tbluProducts.Product, tblMachineOutput.ShiftMachineID, tblMachineOutput.ProductID
FROM tbluProducts INNER JOIN tblMachineOutput ON tbluProducts.ProductID = tblMachineOutput.ProductID;

Use this in place of the tblMachineoutput.

The only issue is that this will not return the netproduct output. You would have to use another subquery for that.

 

[dhookom]

I also think this might require multiple queries or possibly subqueries. If your database was SQL Server you could use CTEs, SUM(Distinct,) or other solutions.

Duane
Hook'D on Access
MS Access MVP

 

[oxicottin]

Majp, Im not understanding your band Aid? Everything im doing still comes out incorect. I can create a query get the totals per date range but when I sum it, it wants to account for every record.

Thanks,
SoggyCashew.....

 

[oxicottin]

I got it.... Finally!!!!

I made 3 querys:

TEST or main query SQL:

SELECT TEST_LeftJoin.EmployeeName, tbluProducts.Product, TEST2_LeftJoin.CountOfProduct, Sum(TEST_LeftJoin.HoursWkd) AS SumOfHours, Sum(TEST_LeftJoin.NetProd) AS SumOfSNet, Sum([NetProd])/Sum([HoursWkd])*10 AS ProductionAverage, Sum([Delay])/(Sum([HoursWkd])) AS [Delay Percent]
FROM (tbluProducts INNER JOIN TEST_LeftJoin ON tbluProducts.ProductID = TEST_LeftJoin.ProductID) INNER JOIN TEST2_LeftJoin ON tbluProducts.ProductID = TEST2_LeftJoin.ProductID
GROUP BY TEST_LeftJoin.EmployeeName, tbluProducts.Product, TEST2_LeftJoin.CountOfProduct;

TEST_LeftJoin query SQL: This is how I got the HrsWorked per product along with a few other needed fields....

SELECT DISTINCT tbluProducts.ProductID, [EmpLName] & ", " & [EmpFName] AS EmployeeName, Sum(tblMachineOutput.NetProduct) AS NetProd, tblShiftMachinesRan.HrsWorked AS HoursWkd, tblShiftMachinesRan.DelayTime AS Delay
FROM tbluProducts INNER JOIN (tbluEmployees INNER JOIN ((tblShiftDay INNER JOIN tblShiftMachinesRan ON tblShiftDay.ShiftDayID = tblShiftMachinesRan.ShiftDayID) INNER JOIN tblMachineOutput ON tblShiftMachinesRan.ShiftMachineID = tblMachineOutput.ShiftMachineID) ON tbluEmployees.EmployeeID = tblShiftMachinesRan.EmployeeID) ON tbluProducts.ProductID = tblMachineOutput.ProductID
WHERE (((tblShiftDay.ShiftDate) Between [Forms]![frmSwitchboard].[txtStartDate] And [Forms]![frmSwitchboard].[txtEndDate]) AND ((tblShiftMachinesRan.EmployeeID) Like [Forms].[frmSwitchboard].[SubformTreeMenu].[Form].[cboEmployees] & "*"))
GROUP BY tbluProducts.ProductID, [EmpLName] & ", " & [EmpFName], tblShiftMachinesRan.HrsWorked, tblShiftMachinesRan.DelayTime;

TEST2_LeftJoin query SQL: Used to get the acuall count of times the product was ran...

SELECT tbluProducts.ProductID, Count(tbluProducts.Product) AS CountOfProduct
FROM tbluProducts INNER JOIN ((tblShiftDay INNER JOIN tblShiftMachinesRan ON tblShiftDay.ShiftDayID = tblShiftMachinesRan.ShiftDayID) INNER JOIN tblMachineOutput ON tblShiftMachinesRan.ShiftMachineID = tblMachineOutput.ShiftMachineID) ON tbluProducts.ProductID = tblMachineOutput.ProductID
WHERE (((tblShiftDay.ShiftDate) Between [Forms]![frmSwitchboard].[txtStartDate] And [Forms]![frmSwitchboard].[txtEndDate]))
GROUP BY tbluProducts.ProductID, tblShiftMachinesRan.EmployeeID
HAVING (((tblShiftMachinesRan.EmployeeID) Like [Forms].[frmSwitchboard].[SubformTreeMenu].[Form].[cboEmployees] & "*"));

Thanks,
SoggyCashew.....