This is a SQL query, specifically formatted for Microsoft Access,...

August 25, 2025 at 11:38 PM

SELECT A.[R_Date], IIF(A.[Cust] = 1, "PWA", IIF(A.[Cust] = 2, "PWC", "GE")) AS Customer, Xref.[IC_PN] AS PartNum_IC, Xref.[DTL_PN] AS PartNum_Detail, Xref.[MFG_PN] AS PartNum_Mfg, Xref.[Mfg_Site] as Mfg_Site, A.[Qty] AS Qty1
FROM PartNum_IC_Xref AS Xref RIGHT JOIN(


SELECT 
       DateSerial(Year([Requested Date]),Month([Requested Date]),1) AS R_Date,
       Part_Number_IC.ID AS ICPN,
        1 As Cust,
       Sum(PWAMRPReport.[Requested Quantity]) AS Qty
FROM Part_Number_IC
INNER JOIN PWAMRPReport ON Part_Number_IC.PartNum_IC = PWAMRPReport.Product
WHERE (((PWAMRPReport.[Requested Date])>=DateSerial(Year(Date()),Month(Date())+1,1)))
GROUP BY 
         DateSerial(Year([Requested Date]),Month([Requested Date]),1),
         Part_Number_IC.ID


UNION ALL

SELECT DateSerial(Year(PWA.Rpt_Date),Month(PWA.Rpt_Date),1) AS R_Date, PWA.IC_PartNum AS ICPN, 1 AS Cust, SUM(Reqd_Qty) AS Qty
FROM Execution_Rpt AS PWA
WHERE PWA.Rpt_Date = [Forms]![Ref_Frm_Qry]![PD_DropDown_Date] AND PWA.Reqd_Date < [Forms]![Ref_Frm_Qry]![PD_DropDown_Date]
GROUP BY DateSerial(Year(PWA.Rpt_Date),Month(PWA.Rpt_Date),1), PWA.IC_PartNum


UNION ALL

SELECT 
       DateSerial(Year([Stat Date]),Month([Stat Date]),1) AS R_Date,
       Part_Number_IC.ID AS ICPN,
       2 As Cust,
       Sum(PWC_Fcast.[Outstand Qty]) AS Qty
FROM Purchase_Order_Cust
RIGHT JOIN (Part_Number_IC
            INNER JOIN PWC_Fcast ON Part_Number_IC.PartNum_IC = PWC_Fcast.[Material Number]) ON (Purchase_Order_Cust.PO_Line = PWC_Fcast.Item)
AND (Purchase_Order_Cust.PO_Number = PWC_Fcast.[Doc Number])
WHERE ((([PWC_Fcast].[Stat Date])>=DateSerial(Year(Date()),Month(Date())+1,1)))
GROUP BY 
         DateSerial(Year([Stat Date]),Month([Stat Date]),1),
         Part_Number_IC.ID

UNION ALL

SELECT DateSerial(Year(PWC.Report_Date),Month(PWC.Report_Date),1) as R_Date, PWC.Material_Number AS ICPN, 2 AS Cust, SUM(Sched_Qty) AS Qty 
FROM Delivery_Maint AS PWC 
WHERE PWC.[Report_Date] = [Forms]![Ref_Frm_Qry]![PD_DropDown_Date] AND PWC.Stat_Date < [Forms]![Ref_Frm_Qry]![PD_DropDown_Date]
GROUP BY  DateSerial(Year(PWC.Report_Date),Month(PWC.Report_Date),1), PWC.Material_Number

UNION ALL

SELECT 
       DateSerial(Year([MRD]),Month([MRD]),1) AS R_Date,
       Part_Number_IC.ID AS ICPN,
       3 As Cust,
       Sum(GESchedLines.[Net Due Quantity]) AS Qty
FROM (GESchedLines
      INNER JOIN Part_Number_IC ON GESchedLines.[Part Number] = Part_Number_IC.PartNum_IC)
LEFT JOIN Purchase_Order_Cust ON (GESchedLines.PO = Purchase_Order_Cust.PO_Number)
AND (GESchedLines.Item = Purchase_Order_Cust.PO_Line)
WHERE ((((GESchedLines.Status)<>"Received")) AND (GESchedLines.[Contract Code] <> "-- ") 
AND (GESchedLines.[Contract Code] IS NOT NULL))
GROUP BY "GE",
      DateSerial(Year([MRD]),Month([MRD]),1),
      Part_Number_IC.ID
)  AS A ON A.[ICPN] = Xref.[IC_ID];

This is a SQL query, specifically formatted for Microsoft Access, used for generating a report by aggregating and joining data from multiple tables. Below is an explanation of what this query does:

High-Level Summary

This query combines data from several tables, performs conditional mappings, groups data at a monthly level, and aggregates quantities (Qty). It associates part numbers with specific customers (PWA, PWC, or GE), and outputs the data along with manufacturing site details and part relationships. The result aggregates and combines data based on the monthly requested quantities and specific criteria.

Detailed Breakdown

From Clause and Outer Query (RIGHT JOIN):
- The main table being queried is PartNum_IC_Xref (aliased as Xref). This table contains cross-referenced part numbers (IC_PN, DTL_PN, etc.).
- The RIGHT JOIN combines it with a subquery (A) that contains aggregated data from multiple sources. The join condition is A.[ICPN] = Xref.[IC_ID].
Data Selection:
- Fields pulled in the SELECT clause include:
  - Date field (R_Date): The monthly bucketed reporting date.
  - Customer mapping: IIF(A.[Cust] = 1, "PWA", IIF(A.[Cust] = 2, "PWC", "GE")) maps customer codes to human-readable names (PWA, PWC, or GE).
  - Several part number fields from Xref (internal part number, detailed part number, manufacturing part number).
  - Manufacturing site and aggregated quantity (Qty1).
Subquery (Multiple UNIONs to Combine Data):
- The subquery combines results from individual SELECT statements, each representing data for a specific customer or source. Each of these queries:
  - Aggregates quantities by customer and monthly reporting date.
  - Aligns customer codes and part numbers across datasets.
- Subquery 1: Monthly quantities by Requested Date for the PWA customer (Cust = 1). This aggregates data from the PWAMRPReport and Part_Number_IC tables for future requests (>= next month).
- Subquery 2: Execution data for the PWA customer based on a specific report date ([Forms]![Ref_Frm_Qry]![PD_DropDown_Date], a user input). Data includes quantities where the requested date is in the past.
- Subquery 3: Forecast data for the PWC customer (Cust = 2). This looks at data from the future (Stat Date >= next month) and aggregates outstanding quantities (Outstand Qty) from the PWC_Fcast table.
- Subquery 4: Delivery data for the PWC customer. It pulls quantities based on a specific report date ([Forms]![Ref_Frm_Qry]![PD_DropDown_Date]) and other criteria.
- Subquery 5: Scheduling data for the GE customer (Cust = 3). This aggregates due quantities (Net Due Quantity) from GE scheduling lines (GESchedLines) excluding received items.
Groupings and Aggregations:
- Each subquery groups by the monthly reporting date (DateSerial(Year(...), Month(...), 1)) and part ID or number. Quantities (Qty) are summed up.
Final Output:
- The final results combine all of this data:
  - For each monthly reporting date (R_Date).
  - For each customer (Cust) mapped to the friendly names PWA, PWC, or GE.
  - With respective part numbers and quantities.

Purpose

This query likely generates a report summarizing forecasted or requested production/part quantities by month for different customers (PWA, PWC, or GE). It associates these quantities with standardized part numbers and their manufacturing sites. The purpose might be:

Facilitating demand planning or manufacturing execution.
Aggregating customer data sourced from multiple tables and formats.
Providing a unified view for decision-making.

Key Notes

The query depends heavily on user-supplied data through a form ([Forms]![Ref_Frm_Qry]![PD_DropDown_Date]).
It uses Access-specific functions like DateSerial, IIF, and Access form references ([Forms]!).
Multiple UNION queries combine data from different customers or sources into a single consistent result format.

Generate your own explanations

Download our vscode extension

Read other generated explanations

Built by @thebuilderjr