Unwanted Duplicates Returning even with DISTINCT

G

Guest

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.
 
M

Michel Walsh

Hi,


If one of the SELECTed field differ, then DISTINCT does its job to return
that record too.

I suggest you locate and remove the extra record form the basic tables,
involved in the JOINs, BEFORE implying them in the various joins in the
actual queries. It seems that the actual queries just uses that existing
(erroneous) record, they (very probably) do not generate it. After all, the
record is in one of the table and the actual queries have no way to know it
is an erroneous record.


Hoping it may help,
Vanderghast, Access MVP

kt said:
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.
Click to expand...
 
Top