Thanks in advance:
Background: Access 2003 Database with Linked Tables to multiple sources for
the purpose of reporting
I built 2 queries today utilizing pre-existing tables and linked dbo tables
1. “Transition Crosstab” Utilizing the tbl Transition
SQL CODE:
TRANSFORM Sum(tblTransition.Qty) AS SumOfQty
SELECT tblTransition.MaterialNbr, tblTransition.Date
FROM tblTransition
GROUP BY tblTransition.MaterialNbr, tblTransition.Date
PIVOT tblTransition.DataField;
2. “Reporting Draft” Utilizing as the primary source of data: tbl Inventory
Consolidation Report Filtered
In addition to dbo_Skeda_datProduct_Location and the above named crosstab
query
I used an Inner Join for all three tables utilizing MeterialNbr and
ProductNbr and Selected DISTINCT
SQL CODE:
SELECT DISTINCT tblInventoryConsolidationReport_filtered.MaterialNbr AS SKU,
tblInventoryConsolidationReport_filtered.LocationNbr,
tblInventoryConsolidationReport_filtered.PlannerEmailName,
tblInventoryConsolidationReport_filtered.ProductFamilyName,
tblInventoryConsolidationReport_filtered.HardwareProgramCode,
tblInventoryConsolidationReport_filtered.[HOPS Area],
tblInventoryConsolidationReport_filtered.LicenseTypeName,
tblInventoryConsolidationReport_filtered.ProductUnitName,
tblInventoryConsolidationReport_filtered.ItemName,
tblInventoryConsolidationReport_filtered.CodeName,
tblInventoryConsolidationReport_filtered.LicenseCountCode,
tblInventoryConsolidationReport_filtered.Intransits AS Intransit_13Wk,
tblInventoryConsolidationReport_filtered.[Inventory $] AS [Inventory $_13WK],
tblInventoryConsolidationReport_filtered.Backorders,
tblInventoryConsolidationReport_filtered.Forecast AS Forecast_13Week,
tblInventoryConsolidationReport_filtered.Sellin,
tblInventoryConsolidationReport_filtered.Standard_USD AS COGS,
tblInventoryConsolidationReport_filtered.[Inventory>13Weeks],
tblInventoryConsolidationReport_filtered.[Inventory$>13Weeks],
tblInventoryConsolidationReport_filtered.runDate,
tblInventoryConsolidationReport_filtered.[Total Inventory] AS Inventory_13WK,
[Kevin Transition_Crosstab].Date, [Kevin Transition_Crosstab].[1 Inventory],
[Kevin Transition_Crosstab].[2 Sales], [Kevin Transition_Crosstab].[3
Forecast], [Kevin Transition_Crosstab].[4 InTransit], [Kevin
Transition_Crosstab].[5-PREQ], dbo_SCEDA_datProductLocation.LotSizeProfile,
dbo_SCEDA_datProductLocation.SkuStratCode,
dbo_SCEDA_datProductLocation.SafetyStock
FROM (tblInventoryConsolidationReport_filtered INNER JOIN [Kevin
Transition_Crosstab] ON
tblInventoryConsolidationReport_filtered.MaterialNbr=[Kevin
Transition_Crosstab].MaterialNbr) INNER JOIN dbo_SCEDA_datProductLocation ON
tblInventoryConsolidationReport_filtered.MaterialNbr=dbo_SCEDA_datProductLocation.ProductNbr
WHERE (((tblInventoryConsolidationReport_filtered.MaterialNbr)=[Kevin
Transition_Crosstab].MaterialNbr And
(tblInventoryConsolidationReport_filtered.MaterialNbr)=dbo_SCEDA_datProductLocation.ProductNbr));
The issue is related to tbl dbo Skeda datProduct Location which I am only
utilizing to add fields: SkuStratCode (not initiating duplicate returns) and
Safety Stock as well as LotSizeProfile both of these fields are returning an
additional row (2 total additions) with all fields duplicated except Safety
Stock which erroneously returns as Zero.
--
kt
|
Similar Threads
