Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

How to summarize a Complex Query subtotals and averages

I have a query that pulls information from a large number of tables
Basically is information regarding warehousing
I have for every part number anywhere from  10 to 100  records that include the following fields.
All the following fields REPEAT EXACTLY THE SAME On EVERY ROW Returned for the PART NUMBER

Date, location ,Planner_ID ,Planner_Name, Product, Description,Basic_UM, locationRC, TotalCIPRC, On_OrderRCN, OnHandRCN

All these fields are different for every row on the returned results
CIPDC , MTDDemandDC ,  ForecastDC

What I need to Do is a query that will have ONLY one record for every part number.
And for the NON repeating fields It will give me the sum  for CIPDC , MTDDemandDC ,  ForecastDC.

Once I have this I can perform calculations on the data  that I need to do.

It may be an easy question but I spent all day getting this information out of three separate systems (If you’ve dealt with SAP and AS400 at the same time you’d know what I’m talking about).

If you have any question please do let me know.
As we speak I’m checking my A2KDH to see if I can get reoriente but I seem to have ran out of steam for today and I need to deliver this report.
0
TOPIO
Asked:
TOPIO
  • 2
  • 2
1 Solution
 
GreymanMSCCommented:
SELECT
  PART_NUMBER, Date, location ,Planner_ID ,Planner_Name, Product, Description,
  Basic_UM, locationRC, TotalCIPRC, On_OrderRCN, OnHandRCN ,
  SUM(CIPDC) AS TotalCIPDC, SUM(MTDDemandDC) As TotalMTDDemandDC,  SUM(ForecastDC) As TotalForecastDCl
FROM _your_from_clause_goes_here_
WHERE _your_where_clause_goes_here_
GROUP BY
  PART_NUMBER, Date, location ,Planner_ID ,Planner_Name, Product, Description,
  Basic_UM, locationRC, TotalCIPRC, On_OrderRCN, OnHandRCN , SUM(CIPDC) AS CIPDC_Total
ORDER BY PART_NUMBER
0
 
GreymanMSCCommented:
Correction (cut and pasted too much)

SELECT
  PART_NUMBER, Date, location ,Planner_ID ,Planner_Name, Product, Description,
  Basic_UM, locationRC, TotalCIPRC, On_OrderRCN, OnHandRCN ,
  SUM(CIPDC) AS TotalCIPDC, SUM(MTDDemandDC) As TotalMTDDemandDC,  SUM(ForecastDC) As TotalForecastDCl
FROM _your_from_clause_goes_here_
WHERE _your_where_clause_goes_here_
GROUP BY
  PART_NUMBER, Date, location ,Planner_ID ,Planner_Name, Product, Description,
  Basic_UM, locationRC, TotalCIPRC, On_OrderRCN, OnHandRCN
ORDER BY PART_NUMBER
0
 
TOPIOAuthor Commented:
I completed the query  as follows

SELECT
qryIntegratedDemandQuery.Date,   qryIntegratedDemandQuery.Planner_ID,
qryIntegratedDemandQuery.Planner_Name, qryIntegratedDemandQuery.Product,
qryIntegratedDemandQuery.Description, qryIntegratedDemandQuery.Basic_UM,
Sum(qryIntegratedDemandQuery.CIPDC) AS CIP_DC, Avg(qryIntegratedDemandQuery.TotalCIPRC) AS CIP_RC,
Sum(qryIntegratedDemandQuery.MTDDemandDC) AS MTDDemand_DC, Avg(qryIntegratedDemandQuery.On_OrderRCN) AS On_OrderRC, Sum(qryIntegratedDemandQuery.ForecastDC) AS Forecast_DC,
([CIP_DC]+[CIP_RC]) AS ActualCIP, (ActualCIP+On_OrderRC) AS PipelineCIP,
(ActualCIP/Forecast_DC) AS ActualMOS,  (PipelineCIP/Forecast_DC) AS PipelineMOS

FROM qryIntegratedDemandQuery

GROUP BY
qryIntegratedDemandQuery.Date, qryIntegratedDemandQuery.Planner_ID, qryIntegratedDemandQuery.Planner_Name, qryIntegratedDemandQuery.Product, qryIntegratedDemandQuery.Description, qryIntegratedDemandQuery.Basic_UM

HAVING
 (((qryIntegratedDemandQuery.Date)=[Forms]![frmReporter]![CB_Date]) AND ((qryIntegratedDemandQuery.Planner_ID)="050")) OR (((qryIntegratedDemandQuery.Date)=[Forms]![frmReporter]![CB_Date]) AND ((qryIntegratedDemandQuery.Planner_ID)="052")) OR (((qryIntegratedDemandQuery.Date)=[Forms]![frmReporter]![CB_Date]) AND ((qryIntegratedDemandQuery.Planner_ID)="056"))
ORDER BY
qryIntegratedDemandQuery.Product;


the only thingI'm missing is how to eliminate the ERROR# Values that I get when the Forecast_DC variable is Zero
when that problems happens in a report I know how to use the IIF.
like  

=IIf([RPT_Distribution].[Report]![Text17]=0,"N/A",[Text34]/[RPT_Distribution].[Report]![Text17])

But I don't  know how to do this in my query
0
 
TOPIOAuthor Commented:
Solved I did it as
IIf([Forecast_DC]=0,"N/A",(ActualCIP/Forecast_DC)) AS ActualMOS,
IIf([Forecast_DC]=0,"N/A",(PipelineCIP/Forecast_DC)) AS PipelineMOS

But how would I format the result from this calculation?
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now