Create Query That Shows Single Line Entry For Each Product Then Do Some Math

[Repent]

I am trying to create a query that will output only one line per product, giving Summed totals per product of LF Produced (tblProductionNumbers) and Manhours (tblManHours). The query would then divide LF Produced by Manhours for each product to give the value for "Productivity".

Do I need to create several queries to do this?

Below is the code I have so far, and it does output a single product name per line in the query. I just need to add in the info for ManHours from the tblManhours table so that it is Summed for each product before that sum is divided into LF Produced.

 SELECT tblProductList.[Part Number], g.Sum_Of_LF_Produced AS LFProduced
FROM tblProductList INNER JOIN (SELECT tblProductionNumbers.ProductID, 
                  Sum(tblProductionNumbers.[LF Run]) AS Sum_Of_LF_Run, 
                  Sum(tblProductionNumbers.[LF Produced]) AS Sum_Of_LF_Produced
               FROM tblProductionHours 
                   INNER JOIN (tblProductList 
                       INNER JOIN tblProductionNumbers ON tblProductList.ID = tblProductionNumbers.ProductID) 
                   ON tblProductionHours.ID = tblProductionNumbers.TimeID
               WHERE ((tblProductionHours.Time In ("END-Days","End-Nights")) AND 
                      (DatePart("ww",[ProductionDate])=DatePart("ww",Date())) AND 
                      (Year([ProductionDate])=Year(Date()))) OR ((DatePart("ww",[ProductionDate])="*"))
               GROUP BY tblProductionNumbers.ProductID
         )  AS g ON tblProductList.ID = g.ProductID
GROUP BY tblProductList.[Part Number], g.Sum_Of_LF_Produced;

 

[Privateer]

Repent,
You might be over cooking this with a sub query and all the criteria. Your observation that you need more queries would be my recommendation.
Create a sum query on the tblProductionNumbers table and make sure you include the primary key field, tblProductionNumbers.ProductID. Then create a sum query on the tblManHours table and include the foreign key relating it back to the production numbers table but leave out the primary key. Then a third select query on the tblProductList with the primary key tblProductList.ID. So now the Product List and Numbers can be related to each other, and the Numbers and Man Hours can be linked.
Additionally, you need to add two more fields to each of the three queries. With the date field in each create an alias for the week (TheWeek) and the year (TheYear) using your datepart function. I am not sure what is going on with the time so I am skipping it for right now.
Now create a fourth query and put all three in there. Draw relationships (lines) from the tblProductList.ID to the Number tables and from Numbers to ManHours, AND draw lines from the tblProductList.TheWeek to tblProductionNumbers.TheWeek AND tblManHours.TheWeek. Then do the same thing for TheYear fields. By drawing these three lines, you are aligning the data up by year and week and the primary key, which I think is what you are looking for. Then for the criteria, under the field ProductionDate.TheYear just put =Year(Date()). I don't understand the wild card for the ProductionDate equal to anything, but I would leave it out until you get this to work and see if you still need it. Let me know how it works out.
Good Luck
Privateer

 

[Repent]

Repent,
You might be over cooking this with a sub query and all the criteria. Your observation that you need more queries would be my recommendation.
Create a sum query on the tblProductionNumbers table and make sure you include the primary key field, tblProductionNumbers.ProductID. Then create a sum query on the tblManHours table and include the foreign key relating it back to the production numbers table but leave out the primary key. Then a third select query on the tblProductList with the primary key tblProductList.ID. So now the Product List and Numbers can be related to each other, and the Numbers and Man Hours can be linked.
Additionally, you need to add two more fields to each of the three queries. With the date field in each create an alias for the week (TheWeek) and the year (TheYear) using your datepart function. I am not sure what is going on with the time so I am skipping it for right now.
Now create a fourth query and put all three in there. Draw relationships (lines) from the tblProductList.ID to the Number tables and from Numbers to ManHours, AND draw lines from the tblProductList.TheWeek to tblProductionNumbers.TheWeek AND tblManHours.TheWeek. Then do the same thing for TheYear fields. By drawing these three lines, you are aligning the data up by year and week and the primary key, which I think is what you are looking for. Then for the criteria, under the field ProductionDate.TheYear just put =Year(Date()). I don't understand the wild card for the ProductionDate equal to anything, but I would leave it out until you get this to work and see if you still need it. Let me know how it works out.
Good Luck
Privateer

I'll give this a try and let you know.

 

[Repent]

Then create a sum query on the tblManHours table and include the foreign key relating it back to the production numbers table but leave out the primary key. Then a third select query on the tblProductList with the primary key tblProductList.ID. So now the Product List and Numbers can be related to each other, and the Numbers and Man Hours can be linked.


Additionally, you need to add two more fields to each of the three queries. With the date field in each create an alias for the week (TheWeek) and the year (TheYear) using your datepart function.

Good Luck
Privateer

Can you expound on what you mean with these two tasks? Not sure what you mean by foreign key and relating it back?

Don't know what you mean by creating an alias for the week and year using the datepart function?

I have an idea what you mean here but I don't want to guess with so many pieces to the puzzle.

 

[Repent]

hmmmmm, I may have this figured out and running. Looks good so far. Also created a "grandtotal" using the same idea.

A BIG THANK YOU to Privateer!!