Assistance with this query

G

Guest

Plant_Cd, Press_Cd, Gross: [TotalShots]*[Cavity_Cnt] | TotalShots: (([EndProdctn-Cntr]-[BeginInjection-Cntr])) | ComputedStndrdCycle_Nbr: CDbl(IIf([StndrdCycle_Nbr]>0,IIf([AverageCycleTme]<[StndrdCycle_Nbr],[AverageCycleTme],[StndrdCycle_Nbr]),"")) | AdjustdUpTme: ([TotalShots]*[ComputedStndrdCycle_Nbr])/60 | AverageCycleTme: (nz([StrtUpCycle_Nbr])+nz([EndOfCycle_Nbr]))/2 | DO: [CalcUpTme]/[Utilization_Min] | CycleTimeEff: [AdjustdUpTme]/[CalcUpTme] | QR: [Accept_Qty]/[Gross] | TRS: [DO]*[CycleTimeEff]*[QR] | Accept_Qty | Availabilty_Min | Utilization_Min | CalcUpTme: (([EndProdctn-Cntr]-[BeginInjection-Cntr])*[AverageCycleTme])/6

The above are the fields in a query that I'm tryying to group on, currently in it's ungrouped stage is has 212 records. However in a grouped query I should end up with 53 records. I have seperated the query columns with a "|" pipe, I guess it's called. I am having a bit of a hard time determining where to use: "Group", "Sum" or "Expression". Below is a copy of the SQL string to better assist those of you who are the guru's in this matter

SELECT Job_Calculation_by_Plant.Plant_Cd, Job_Calculation_by_Plant.Press_Cd, [TotalShots]*[Cavity_Cnt] AS Gross, (([EndProdctn-Cntr]-[BeginInjection-Cntr])) AS TotalShots, CDbl(IIf([StndrdCycle_Nbr]>0,IIf([AverageCycleTme]<[StndrdCycle_Nbr],[AverageCycleTme],[StndrdCycle_Nbr]),"")) AS ComputedStndrdCycle_Nbr, ([TotalShots]*[ComputedStndrdCycle_Nbr])/60 AS AdjustdUpTme, (nz([StrtUpCycle_Nbr])+nz([EndOfCycle_Nbr]))/2 AS AverageCycleTme, [CalcUpTme]/[Utilization_Min] AS DO, [AdjustdUpTme]/[CalcUpTme] AS CycleTimeEff, [Accept_Qty]/[Gross] AS QR, [DO]*[CycleTimeEff]*[QR] AS TRS, Job_Calculation_by_Plant.Accept_Qty, Job_Calculation_by_Plant.Availabilty_Min, Job_Calculation_by_Plant.Utilization_Min, (([EndProdctn-Cntr]-[BeginInjection-Cntr])*[AverageCycleTme])/60 AS CalcUpTm
FROM Job_Calculation_by_Plant

Any assitance in this matter will be appreciated

Thanks
 
C

Chris2

Terri said:
Plant_Cd, Press_Cd, Gross: [TotalShots]*[Cavity_Cnt] | TotalShots:
Click to expand...
(([EndProdctn-Cntr]-[BeginInjection-Cntr])) | ComputedStndrdCycle_Nbr:
CDbl(IIf([StndrdCycle_Nbr]>0,IIf([AverageCycleTme]<[StndrdCycle_Nbr],[Averag
eCycleTme],[StndrdCycle_Nbr]),"")) | AdjustdUpTme:
([TotalShots]*[ComputedStndrdCycle_Nbr])/60 | AverageCycleTme:
(nz([StrtUpCycle_Nbr])+nz([EndOfCycle_Nbr]))/2 | DO:
[CalcUpTme]/[Utilization_Min] | CycleTimeEff: [AdjustdUpTme]/[CalcUpTme] |
QR: [Accept_Qty]/[Gross] | TRS: [DO]*[CycleTimeEff]*[QR] | Accept_Qty |
Availabilty_Min | Utilization_Min | CalcUpTme:
(([EndProdctn-Cntr]-[BeginInjection-Cntr])*[AverageCycleTme])/60
The above are the fields in a query that I'm tryying to group on,
Click to expand...
currently in it's ungrouped stage is has 212 records. However in a grouped
query I should end up with 53 records. I have seperated the query columns
with a "|" pipe, I guess it's called. I am having a bit of a hard time
determining where to use: "Group", "Sum" or "Expression". Below is a copy of
the SQL string to better assist those of you who are the guru's in this
matter:
SELECT Job_Calculation_by_Plant.Plant_Cd,
Click to expand...
Job_Calculation_by_Plant.Press_Cd, [TotalShots]*[Cavity_Cnt] AS Gross,
(([EndProdctn-Cntr]-[BeginInjection-Cntr])) AS TotalShots,
CDbl(IIf([StndrdCycle_Nbr]>0,IIf([AverageCycleTme]<[StndrdCycle_Nbr],[Averag
eCycleTme],[StndrdCycle_Nbr]),"")) AS ComputedStndrdCycle_Nbr,
([TotalShots]*[ComputedStndrdCycle_Nbr])/60 AS AdjustdUpTme,
(nz([StrtUpCycle_Nbr])+nz([EndOfCycle_Nbr]))/2 AS AverageCycleTme,
[CalcUpTme]/[Utilization_Min] AS DO, [AdjustdUpTme]/[CalcUpTme] AS
CycleTimeEff, [Accept_Qty]/[Gross] AS QR, [DO]*[CycleTimeEff]*[QR] AS TRS,
Job_Calculation_by_Plant.Accept_Qty,
Job_Calculation_by_Plant.Availabilty_Min,
Job_Calculation_by_Plant.Utilization_Min,
(([EndProdctn-Cntr]-[BeginInjection-Cntr])*[AverageCycleTme])/60 AS
CalcUpTme
FROM Job_Calculation_by_Plant;

Any assitance in this matter will be appreciated.

Thanks,
Click to expand...

Query above realigned for readability:

SELECT Job_Calculation_by_Plant.Plant_Cd
,Job_Calculation_by_Plant.Press_Cd
,[TotalShots] * [Cavity_Cnt] AS Gross
,(([EndProdctn-Cntr] - [BeginInjection-Cntr])) AS TotalShots
,CDbl(IIf([StndrdCycle_Nbr] > 0,
IIf([AverageCycleTme]<[StndrdCycle_Nbr],
[AverageCycleTme],
[StndrdCycle_Nbr]),"")) AS ComputedStndrdCycle_Nbr
,([TotalShots] * [ComputedStndrdCycle_Nbr]) / 60 AS AdjustdUpTme
,(nz([StrtUpCycle_Nbr]) + nz([EndOfCycle_Nbr])) / 2 AS
AverageCycleTme
,[CalcUpTme] / [Utilization_Min] AS DO
,[AdjustdUpTme] / [CalcUpTme] AS CycleTimeEff
,[Accept_Qty] / [Gross] AS QR
,[DO] * [CycleTimeEff] * [QR] AS TRS
, Job_Calculation_by_Plant.Accept_Qty
,Job_Calculation_by_Plant.Availabilty_Min
,Job_Calculation_by_Plant.Utilization_Min
,(([EndProdctn-Cntr] - [BeginInjection - Cntr]) * [AverageCycleTme])
/ 60 AS CalcUpTme
FROM Job_Calculation_by_Plant;
GROUP BY <column-list>


On the GROUP BY clause, include every line on the SELECT clause that does
not have a "totals" functions (Scalar function), SUM, AVG, COUNT, etc.


Sincerely,

Chris O.
 
Top