Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Queries returning unwanted duplicates

Discussion in 'Information Technology' started by kt, Jul 28, 2006.

  1. kt

    kt
    Expand Collapse
    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.


    --
    kt
     
  2. Loading...

    Similar Threads Forum Date
    Importance of Hukamnama - Some Queries Sikh Sikhi Sikhism Jun 14, 2009
    Queries History of Sikhism Oct 26, 2006
    Christianity The Path of Returning to God Interfaith Dialogues Feb 6, 2011
    Returning to SPN Announcements Nov 2, 2006
    Returning to sikhi, actually... Announcements Feb 14, 2006

Share This Page