techwriterAR
MIS
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
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