Results 1 to 5 of 5

Grouping records in MS Query

This is a discussion on Grouping records in MS Query within the Excel Questions forums, part of the Question Forums category; I have a simple query running in Excel using MS Query. It is pulling several columns of data but I ...

  1. #1
    New Member
    Join Date
    Jul 2003
    Posts
    17

    Default Grouping records in MS Query

    I have a simple query running in Excel using MS Query. It is pulling several columns of data but I want to group the data and create summary for a couple of fields. Can I do that in Query without writing SQL code?

    Thanks

  2. #2
    Board Regular LTunnicliffe's Avatar
    Join Date
    May 2003
    Location
    Round Rock, TX
    Posts
    767

    Default Re: Grouping records in MS Query

    Maybe you could post the SQL here and someone kind and bored enough will make the changes for you. It is possible. We just don't know what you want yet.
    Loren

    The numbers don't lie, but sometimes they don't tell the whole truth.

  3. #3
    New Member
    Join Date
    Jul 2003
    Posts
    17

    Default Re: Grouping records in MS Query

    Here is the SQL code that Query has created so far:

    SELECT E1000091.SKU, E100000F.SKU_DESC, E1000091.STATUS, E1000091.QTY_LOCATED, E1000091.QTY_RESERVED, E1000091.QTY_ALLOCATED, E100000F.SELLING_UOM, E100000F.REORDER_PT, E100000F.REORDER_QTY, E100000F.MAX_ON_HAND_QTY, E100000F.LEAD_TIME_TO_PROCURE, E100000F.ITEM_TYPE, E100000F.COMMODITY_CODE, E100000F.CC_CODE, E100000F.SUPPLIER_1

    FROM E100000F E100000F, E1000091 E1000091

    WHERE E100000F.COMPANY_ID = E1000091.COMPANY_ID AND E100000F.SKU = E1000091.SKU AND ((E1000091.QTY_LOCATED
    ORDER BY E100000F.SUPPLIER_1

    I would like to group the data by 'E1000091.SKU' and create a sum field for E1000091.QTY_LOCATED, E1000091.QTY_RESERVED, E1000091.QTY_ALLOCATED, and then just display all the other fields as the group header.


    Andre

  4. #4
    Board Regular LTunnicliffe's Avatar
    Join Date
    May 2003
    Location
    Round Rock, TX
    Posts
    767

    Default Re: Grouping records in MS Query

    Here is the SQL:

    Code:
    SELECT E1000091.SKU, E100000F.SKU_DESC, E1000091.STATUS, E1000091.QTY_LOCATED, Sum(E1000091.QTY_RESERVED) as SumOfQtyReserved, Sum(E1000091.QTY_ALLOCATED) as SumOfQtyAllocated, E100000F.SELLING_UOM, E100000F.REORDER_PT, E100000F.REORDER_QTY, E100000F.MAX_ON_HAND_QTY, E100000F.LEAD_TIME_TO_PROCURE, E100000F.ITEM_TYPE, E100000F.COMMODITY_CODE, E100000F.CC_CODE, E100000F.SUPPLIER_1 
    FROM E100000F E100000F, E1000091 E1000091 
    WHERE E100000F.COMPANY_ID = E1000091.COMPANY_ID AND E100000F.SKU = E1000091.SKU AND ((E1000091.QTY_LOCATED 
    GROUP BY E1000091.SKU
    ORDER BY E100000F.SUPPLIER_1
    Note that there appears to be a problem with your SQL in the WHERE statement because there are two (( but no )). I'm not sure what is causing that problem.

    Hope this helps!
    Loren

    The numbers don't lie, but sometimes they don't tell the whole truth.

  5. #5
    New Member
    Join Date
    Nov 2011
    Posts
    6

    Default Re: Grouping records in MS Query

    Can you please help me with this code. I tried my best I couldnt make it to work in Excel MS Query

    SELECT GTORHD.HENTDT, Sum(GTORDT.DTOTAL) as SumOfDTOTAL
    FROM POSPRO.QS36F.GTORDT GTORDT, POSPRO.QS36F.GTORHD GTORHD
    WHERE GTORDT.DORD# = GTORHD.HORD# AND ((GTORHD.HENTDT>20140301) AND (GTORHD.HORSTS<>'C') AND (GTORDT.DSTATS<>'C') AND (GTORHD.HORSRC='W'))

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com