Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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

Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page