Need help with grouping in query

[techwriterAR]

I am using Access 2003 with windows XP. I have a performance tracking db with the following fields: cellID, product code, date, qty ordered, qty received, actcrewsize, start time, end time, goal time. I use a query to calculate the performance. In the performance query, I have a formula that calculates time worked in minutes; a formula that converts goal time to minutes; and a formula that calculates efficiency. Sometimes I will have 2 or more performance sheets that have the same product code and date but the qty ordered and received are different as well as the start and end times. When I run the query, it lists each record with the same product code on separate lines. What I need is a way to add the data together and calculate an average. I think I need to use grouping in my query but I cannot get it right.

Here is the Performance Query:
SELECT tblFabricationCells.Product, tblFabricationCells.ActualQty, tblFabricationCells.ActCrewSize, Shift(tblFabricationCells!StartTime,tblFabricationCells!EndTime) AS ElapsedTime, ([actualqty]/[requiredqty])*[goaltime]*60 AS GoalRunTime, [GoalRunTime]/[ElapsedTime] AS CellEff, tblFabricationCells.Date
FROM tblCellIDs INNER JOIN tblFabricationCells ON tblCellIDs.CellID = tblFabricationCells.CellID
WHERE (((tblFabricationCells.Date)=#8/23/2007#) AND ((tblFabricationCells.CellID)="2"));

Which returns this data:

Product ActQty Crew ElapsedTime GoalRunTime CellEff Date
8003701 172 1 140.00 147.43 105.31% 8/23/2007
8003701 778 1 705.00 666.86 94.59% 8/23/2007
8003795 690 2 555.00 414.00 74.59% 8/23/2007
80806111bag 330 1 180.00 132.10 73.39% 8/23/2007
80806111assm 670 1 270.00 402.00 148.89% 8/23/2007
80806111assm 185 1 120.00 111.00 92.50% 8/23/2007

I tried to create a group by clause as shown in query 2:

Query 2:
SELECT tblFabricationCells.Product, tblFabricationCells.ActualQty, tblFabricationCells.ActCrewSize,sum(Shift(tblFabricationCells!StartTime,tblFabricationCells!EndTime)) AS ElapsedTime,
sum(([actualqty]/[requiredqty])*[goaltime]*60) AS GoalRunTime,sum([GoalRunTime]/[ElapsedTime]) AS CellEff, tblFabricationCells.Date
FROM tblCellIDs INNER JOIN tblFabricationCells ON tblCellIDs.CellID=tblFabricationCells.CellID
WHERE (((tblFabricationCells.Date)=#8/23/2007#) AND ((tblFabricationCells.CellID)="2"))
group by tblFabricationCells.Product, tblFabricationCells.ActualQty,tblFabricationCells.ActCrewSize, tblFabricationCells.Date;

When I run query 2 I get the following error messge: “Subqueries cannot be used in the expression (sum([GoalRunTime])/[ElapsedTIme])).

Any help will be greatly appreciated.

gjeffcoat

 

[ClydeData]

Use a make table query from you Sub Query to make a temporary Table.

Then Run your Q2 on the temporary table

Hope this helps

Jimmy

 

[techwriterAR]

ClydeData,

Thanks for your response. Let me see if I understand what you are suggesting. First I create a make table using the performance query. Then I use query 2 on the make table to get the final result which should have added the like-products together. Okay, I will give that a try and see what happens.

Thanks,
G.Jeffcoat