Grouping records in MS Query

aduval

New Member
Joined
Jul 18, 2003
Messages
17
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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. :)
 
Upvote 0
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<Qty_Reserved+Qty_Allocated) AND (E100000F.STATUS='A') AND (E1000091.WAREHOUSE_ID=1) OR (E1000091.QTY_LOCATED<Reorder_pt))

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
 
Upvote 0
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!
 
Upvote 0
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'))
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top