I'm having trouble figuring out how to get a "sum" over groupings.
Bascially from a MaterialAssign table, material is assigned to a machine.
A second table, MaterialUsage, has the same material, along with gallons, month and year. (This is were the end-user inputs to) A third table, RunHours, takes in the machine's id number, run hours, month and year. My objective is to show the percentage of production of the materials used on a machine, but it's complicated by the fact that not all the machines use the all the material.
I can easily get a report showing the machine with a breakdown of the material it used, it's runhours, month and year.
I just have been struggling with figuring out how to get the total material only used by certain machines so then I can figure by the percentage of run hours to total hours run, what the likely percentage of production material is used on a specific machine.
Any help or suggestion are welcome... I done Db work before, but I'm new to MS Access 2000.
A Material Assign table was created in which the materials are assigned to a machine, or in the demo case, a person. Beer, wine and soda are assigned to Carol, soda to Jessica and beer, wine and whiskey to ron.
I have a demo table of MaterialUsage which has a column of material; beer, wine, whiskey and soda, with another column Gals, Month column and Year column. The end-user would use a form to input the monthly consumption of the materials.
The last table of concern has a column of the machine's id, or this case, the person's name, a column of Hours of operation, Month and Year. Basically, jessica runs 250 hours, carol at 500 hours and ron at 750 hours per month. Another form would allowed the end-user to input hours of operation per month.
The attempt is to get a report grouped by the person's name (done), showing what material they consumed (done), the hours of operation and relative percentage to the three total hours by all three parties. The problem I have begins in the Percent of Production, that is; the relation of hours that Carol and Jess have to each other, and the fact that they are the only two that consume soda, they should have percentages of production of 66% and 33%. Likewise, since ROn is the only one that consumes whiskey, that percentage of production should always be 100%. no matter what the actual gallons used are. That's where I can't get to. How to get MS Access to give me that percentage back correctly. I'm hoping that I'm to close to the trees to see the forest....
I have a possible way to do this, by using a subquery, but now I'm having trouble getting a query parameter to be used by the subquery.
SELECT DISTINCT MaterialAssign.Material, MaterialAssign.PressAssign_id, Sum(RunHours.Hours) AS RHHours, RunHours.Month, RunHours.Year, MaterialUsage.Lbs, MaterialUsage.Gals, Sum(MaterialUsage.Gals) AS RSGals,
(SELECT DISTINCT Sum(RunHours.Hours)
FROM MaterialUsage INNER JOIN (MaterialAssign INNER JOIN RunHours ON MaterialAssign.PressAssign_id = RunHours.RunHours_id) ON (MaterialUsage.Material = MaterialAssign.Material) AND (MaterialUsage.Month = RunHours.Month) AND (MaterialUsage.Year = RunHours.Year)
WHERE MaterialAssign.Material= 'MaterialAssign.Material'
GROUP BY MaterialAssign.Material, RunHours.Month, RunHours.Year
HAVING (((MaterialAssign.Material)=[MaterialAssign.Material]) AND ((RunHours.Month)=[Select Month]) AND ((RunHours.Year)="03"))) AS MaterialTotalHours
FROM MaterialUsage INNER JOIN ((MaterialAssign INNER JOIN Material ON MaterialAssign.Material=Material.Material) INNER JOIN RunHours ON MaterialAssign.PressAssign_id=RunHours.RunHours_id ) ON (MaterialUsage.Material=MaterialAssign.Material) AND (MaterialUsage.Month=RunHours.Month) AND (MaterialUsage.Year=RunHours.Year)
GROUP BY MaterialAssign.Material, MaterialAssign.PressAssign_id, RunHours.Month, RunHours.Year, MaterialUsage.Lbs, MaterialUsage.Gals
HAVING (((RunHours.Month)=[Select Month such as May]) AND ((RunHours.Year)="03"));