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

    Unanswered: How do I add "GroupBy" to an existing Make Table Query?

    Hello,

    I have a Make Table Query that's creating duplicates and I learned that if I put a "GroupBy" in the title, I can eliminate the duplicate records.

    The challenge is, I don't know where and how to do that and can't find anything that's remotely my skill level to show me how.

    The SQL VIEW on my Query looks like this
    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.PFX_NUM = ECDB2_HIERARCHY.PFX_NUM) AND (ITEM.STK_NUM = ECDB2_HIERARCHY.STK_NUM);

  2. #2
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    What version of Access are you using?
    Basically you just need to switch grouping on in the query design window.
    Regards
    JD

    Software-Matters

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

    Thank you for responding. I am using Access 2003. Ultimately I have achieved no duplicates with a built in Access query that is at the below URL (posting for other people that may be looking for a duplicate solution other than this)

    I would much rather not have duplicates to begin with so can you explain how to "switch grouping on in the query design window"?

    If you mean drag the boxes in different positions (probably not) I did try that.

  4. #4
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    You can switch on grouping by clicking on the Totals button in the toolbar (This is the button with the summation symbol - it looks like a sideways M).

    If you don't see this button try selecting View Totals from the menu.
    Regards
    JD

    Software-Matters

Posting Permissions

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