Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2010
    Posts
    87

    Unanswered: Joining Tables & Getting Duplicates - How do I avoid duplicates?

    Hello,

    Maybe you know how?

    I'm joining 4 tables of data into one, it's complicated and for what its worth I have to join them. The challenge is that I'm getting duplicate rows of data.

    I had looked for things that would cause the duplicates and removed the references to those but its still creating duplicate rows.

    Is there something to add to this query that will make it so I don't get duplicate rows when the tables are joined?

    Code:
    SELECT BRAND.BRND_ID AS BRAND_BRND_ID, BRAND.BRND_NM, BRAND.BRND_LOGO, BRAND.BRND_WEB, BRAND.PROD_SPT_PH_NUM, ECDB2_HIERARCHY.SEQ_ID, ECDB2_HIERARCHY.PFX_NUM AS ECDB2_HIERARCHY_PFX_NUM, ECDB2_HIERARCHY.STK_NUM AS ECDB2_HIERARCHY_STK_NUM, ECDB2_HIERARCHY.ECDB2_LVL_1, ECDB2_HIERARCHY.ECDB2_LVL_1_ID, ECDB2_HIERARCHY.ECDB2_LVL_2, ECDB2_HIERARCHY.ECDB2_LVL_2_ID, ECDB2_HIERARCHY.ECDB2_LVL_3, ECDB2_HIERARCHY.ECDB2_LVL_3_ID, ECDB2_HIERARCHY.ECDB2_LVL_4, ECDB2_HIERARCHY.ECDB2_LVL_4_ID, ECDB2_HIERARCHY.ECDB2_LVL_5, ECDB2_HIERARCHY.ECDB2_LVL_5_ID, ECDB2_HIERARCHY.ECDB2_LVL_6, ECDB2_HIERARCHY.ECDB2_LVL_6_ID, ITEM.PFX_NUM AS ITEM_PFX_NUM, ITEM.STK_NUM AS ITEM_STK_NUM, ITEM.PROD_NUM, ITEM.UNBT_STK_NUM, ITEM.MFR_SKU_NUM, ITEM.CO_ID AS ITEM_CO_ID, ITEM.VNDR_SH_NM, ITEM.BRND_ID AS ITEM_BRND_ID, ITEM.SKU_GP_ID AS ITEM_SKU_GP_ID, ITEM.SPL_FEAT_BEN_STM, ITEM.PKG_INCL, ITEM.ECDB1_ID, ITEM.PROD_DSC, ITEM.INV_UN_CDE, ITEM.ITEM_WGT, ITEM.ITEM_DEPTH, ITEM.ITEM_WDT, ITEM.ITEM_HGT, ITEM.GTIN_ITEM, ITEM.GTIN_CTN, ITEM.GTIN_BX, ITEM.GTIN_PAL, ITEM.UPC_RTL, ITEM.UPC_CTN, ITEM.CTRY_ORIG_CDE, ITEM.PROD_CLS_CDE, ITEM.PROD_CLS_CDE_VAL, ITEM.LG_ITEM_DSC, ITEM.ASM_CDE, ITEM.RECY_IND, ITEM.MSDS_IND, ITEM.MSDS, ITEM.UNSPSC, ITEM.SGL_IMG, ITEM.ALT_IMG, ITEM.ACT_IND, ITEM.NON_RTRNBLE_CDE, ITEM.SML_PKG_IND, ITEM.PK_QTY, ITEM.PK_UN_CDE, ITEM.VAL_PK, ITEM.KEYWRDS, ITEM.WAR_IND, ITEM.WAR_EXT_DET, ITEM.WAR_COM, ITEM.CON_ITEM_COP, ITEM.STAT_CDE_1, ITEM.STAT_CDE_2, ITEM.STAT_CDE_3, ITEM.LN_DRW, ITEM.NAT_STK_NUM, ITEM.BX_PK_QTY, ITEM.BX_WGT, ITEM.BX_DEPTH, ITEM.BX_WDT, ITEM.BX_HGT, ITEM.BX_PK_UN_CDE, ITEM.CTN_PK_QTY, ITEM.CTN_DEPTH, ITEM.CTN_WDT, ITEM.CTN_HGT, ITEM.CTN_WGT_DEC, ITEM.CTN_PK_UN_CDE, ITEM.CTN_WGT_WHL_NUM, ITEM.SHP_CLS_CDE, ITEM.GRN_IND, ITEM.EPA_CPG_CDE, ITEM.GRN_INFO, ITEM.LIST_AMT, ITEM.PL_ID, ITEM.FOR_MOD_NUM, SKU_GROUP.SKU_GP_ID AS SKU_GROUP_SKU_GP_ID, SKU_GROUP.SKU_GP_NM, SKU_GROUP.SMRY_SLLNG_STM, SKU_GROUP.SLLNG_PNT_1, SKU_GROUP.SLLNG_PNT_2, SKU_GROUP.SLLNG_PNT_3, SKU_GROUP.SLLNG_PNT_4, SKU_GROUP.SLLNG_PNT_5, SKU_GROUP.SLLNG_PNT_6, SKU_GROUP.SLLNG_PNT_7, SKU_GROUP.SLLNG_PNT_8, SKU_GROUP.SLLNG_PNT_9, SKU_GROUP.SLLNG_PNT_10, SKU_GROUP.SH_SLLNG_COP, SKU_GROUP.MED_SLLNG_COP, SKU_GROUP.LG_SLLNG_COP, SKU_GROUP.SKU_GP_IMG, SKU_GROUP.SKU_GP_ALT_IMG, SKU_GROUP.SKU_GP_ICO, SKU_GROUP.CONS_CAT_COP INTO [One Big Table]
    FROM ((SKU_GROUP INNER JOIN ITEM ON SKU_GROUP.SKU_GP_ID = ITEM.SKU_GP_ID) INNER JOIN BRAND ON ITEM.BRND_ID = BRAND.BRND_ID) INNER JOIN ECDB2_HIERARCHY ON (ITEM.STK_NUM = ECDB2_HIERARCHY.STK_NUM) AND (ITEM.PFX_NUM = ECDB2_HIERARCHY.PFX_NUM);

  2. #2
    Join Date
    Aug 2010
    Posts
    87
    Note: The table called "ITEM" that's part of this has the correct amount of records in it and no duplicates. Once it's joined with the other tables, about 20,000 duplicates are made.

    I thought that if somehow I could tell Access that I want to take what's in table "ITEM" and then add to it the correlating data in the other 3 tables "ECDB2", "SKU_GROUP" and "BRAND" into one new table called "ONE BIG TABLE" but without duplicating records that it would work. I guess I'm doing something wrong though.

  3. #3
    Join Date
    Aug 2010
    Posts
    87
    Update:

    I have 40,000 records in one table with no duplicates.
    When I merge the tables, there are 56,000 records of which 16000 are duplicates.
    When I scan for duplicates with a Duplicate Find query, it shows 26,000 duplicates.

    Which means I would delete to many records of a removed the duplicates. I don't know why this is happening but i need a way to merge the table data without causing it to make duplicates.

    i want to keep the count of 40,000 records and only use the records in table name "ITEM" as my base.

    Any advice?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    merge tables
    where are the duplicates coming from?

    Im guessing you are doing a join somewhere
    so short of seeing your SQL AND table design I think it will be tricky to diagnose what is going on
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2010
    Posts
    87
    Hello,

    The SQL from SQL Code View is above. It's an Update Query that's joining the 4 tables together.

    Here it is again just in case it's not visible for some reason.


    Code:
    SELECT BRAND.BRND_ID AS BRAND_BRND_ID, BRAND.CO_ID AS BRAND_CO_ID, BRAND.BRND_NM, BRAND.BRND_LOGO, BRAND.BRND_WEB, BRAND.PROD_SPT_PH_NUM, ECDB2_HIERARCHY.SEQ_ID, ECDB2_HIERARCHY.PFX_NUM AS ECDB2_HIERARCHY_PFX_NUM, ECDB2_HIERARCHY.STK_NUM AS ECDB2_HIERARCHY_STK_NUM, ECDB2_HIERARCHY.ECDB2_LVL_1, ECDB2_HIERARCHY.ECDB2_LVL_1_ID, ECDB2_HIERARCHY.ECDB2_LVL_2, ECDB2_HIERARCHY.ECDB2_LVL_2_ID, ECDB2_HIERARCHY.ECDB2_LVL_3, ECDB2_HIERARCHY.ECDB2_LVL_3_ID, ECDB2_HIERARCHY.ECDB2_LVL_4, ECDB2_HIERARCHY.ECDB2_LVL_4_ID, ECDB2_HIERARCHY.ECDB2_LVL_5, ECDB2_HIERARCHY.ECDB2_LVL_5_ID, ECDB2_HIERARCHY.ECDB2_LVL_6, ECDB2_HIERARCHY.ECDB2_LVL_6_ID, ITEM.PFX_NUM AS ITEM_PFX_NUM, ITEM.STK_NUM AS ITEM_STK_NUM, ITEM.PROD_NUM, ITEM.UNBT_STK_NUM, ITEM.MFR_SKU_NUM, ITEM.CO_ID AS ITEM_CO_ID, ITEM.VNDR_SH_NM, ITEM.BRND_ID AS ITEM_BRND_ID, ITEM.SKU_GP_ID AS ITEM_SKU_GP_ID, ITEM.SPL_FEAT_BEN_STM, ITEM.PKG_INCL, ITEM.ECDB1_ID, ITEM.PROD_DSC, ITEM.INV_UN_CDE, ITEM.ITEM_WGT, ITEM.ITEM_DEPTH, ITEM.ITEM_WDT, ITEM.ITEM_HGT, ITEM.GTIN_ITEM, ITEM.GTIN_CTN, ITEM.GTIN_BX, ITEM.GTIN_PAL, ITEM.UPC_RTL, ITEM.UPC_CTN, ITEM.CTRY_ORIG_CDE, ITEM.PROD_CLS_CDE, ITEM.PROD_CLS_CDE_VAL, ITEM.LG_ITEM_DSC, ITEM.ASM_CDE, ITEM.RECY_IND, ITEM.MSDS_IND, ITEM.MSDS, ITEM.UNSPSC, ITEM.SGL_IMG, ITEM.ALT_IMG, ITEM.ACT_IND, ITEM.NON_RTRNBLE_CDE, ITEM.SML_PKG_IND, ITEM.PK_QTY, ITEM.PK_UN_CDE, ITEM.VAL_PK, ITEM.KEYWRDS, ITEM.WAR_IND, ITEM.WAR_EXT_DET, ITEM.WAR_COM, ITEM.CON_ITEM_COP, ITEM.STAT_CDE_1, ITEM.STAT_CDE_2, ITEM.STAT_CDE_3, ITEM.LN_DRW, ITEM.NAT_STK_NUM, ITEM.BX_PK_QTY, ITEM.BX_WGT, ITEM.BX_DEPTH, ITEM.BX_WDT, ITEM.BX_HGT, ITEM.BX_PK_UN_CDE, ITEM.CTN_PK_QTY, ITEM.CTN_DEPTH, ITEM.CTN_WDT, ITEM.CTN_HGT, ITEM.CTN_WGT_DEC, ITEM.CTN_PK_UN_CDE, ITEM.CTN_WGT_WHL_NUM, ITEM.SHP_CLS_CDE, ITEM.GRN_IND, ITEM.EPA_CPG_CDE, ITEM.GRN_INFO, ITEM.LIST_AMT, ITEM.PL_ID, ITEM.FOR_MOD_NUM, SKU_GROUP.SKU_GP_ID AS SKU_GROUP_SKU_GP_ID, SKU_GROUP.SKU_GP_NM, SKU_GROUP.SMRY_SLLNG_STM, SKU_GROUP.SLLNG_PNT_1, SKU_GROUP.SLLNG_PNT_2, SKU_GROUP.SLLNG_PNT_3, SKU_GROUP.SLLNG_PNT_4, SKU_GROUP.SLLNG_PNT_5, SKU_GROUP.SLLNG_PNT_6, SKU_GROUP.SLLNG_PNT_7, SKU_GROUP.SLLNG_PNT_8, SKU_GROUP.SLLNG_PNT_9, SKU_GROUP.SLLNG_PNT_10, SKU_GROUP.SH_SLLNG_COP, SKU_GROUP.MED_SLLNG_COP, SKU_GROUP.LG_SLLNG_COP, SKU_GROUP.SKU_GP_IMG, SKU_GROUP.SKU_GP_ALT_IMG, SKU_GROUP.SKU_GP_ICO, SKU_GROUP.CONS_CAT_COP INTO [One Big Table]
    FROM ((SKU_GROUP INNER JOIN ITEM ON SKU_GROUP.SKU_GP_ID = ITEM.SKU_GP_ID) INNER JOIN BRAND ON ITEM.BRND_ID = BRAND.BRND_ID) INNER JOIN ECDB2_HIERARCHY ON (ITEM.STK_NUM = ECDB2_HIERARCHY.STK_NUM) AND (ITEM.PFX_NUM = ECDB2_HIERARCHY.PFX_NUM);

  6. #6
    Join Date
    Aug 2010
    Posts
    87
    I tried setting a field in the ITEM table as the Primary Key so I could avoid the duplicates and ran the Table join query again and it didn't work. Still got way to many records.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •