Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
group/sum in ms query
Hi, I have an Excel MS query that works great but brings in too many records for Excel. Can I summarize/group in MS query to reduce the total number of rows (records)? For example: I have daily details about shipments. Data is stored by part #, by container. So the same job can ship every day in several boxes causing many rows. I just need the total by part for the last year. Any way to do this in the Query so I don't have to load all the detail into Excel? Thanks, Keith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
group/sum in ms query
I recorded a macro while setting up a MS Query below. The Command Text portion of the query is the SQL statement. You can see my query has some SUM fields in the SELECT statement. You would also need to apply some filters like WHERE SQL statments. With ActiveSheet.QueryTables.Add( _ Connection:=Array(Array( _ "ODBC;" & _ "DSN=MS Access Database;" & _ "DBQ=C:\temp\working\Book1.mdb;" & _ "DefaultDir=C:\temp\working;" & _ "DriverId=25;" & _ "FIL=MS Access;MaxBufferSize=2048;Pa"), _ Array("geTimeout=5;")), Destination:=Range("A1")) .CommandText = Array( _ "SELECT Count(Sheet1.sName) AS 'Count of sName', " & _ "Sum(Sheet1.ciSEID) AS 'Sum of ciSEID', " & _ "Sum(Sheet1.ciSEqty) AS 'Sum of ciSEqty', " & _ "Sheet1.ciSEvalue, " & _ "Sheet1.ciSECharge, " & _ "Sheet1.ciSEInRefSource, " & _ "Sheet1.ciSE", _ "InRefData" & Chr(13) & "" & Chr(10) & _ "FROM `C:\temp\working\Book1`.Sheet1 Sheet1, " & _ "`C:\temp\working\Book1`.Sheet1 Sheet1_1" & _ Chr(13) & "" & Chr(10) & _ "GROUP BY Sheet1.ciSEvalue, " & _ "Sheet1.ciSECharge, " & _ "Sheet1.ciSEInRefSource, " & _ "Sheet1.ciSEInRefData") .Name = "Query from MS Access Database" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With "Keith" wrote: Hi, I have an Excel MS query that works great but brings in too many records for Excel. Can I summarize/group in MS query to reduce the total number of rows (records)? For example: I have daily details about shipments. Data is stored by part #, by container. So the same job can ship every day in several boxes causing many rows. I just need the total by part for the last year. Any way to do this in the Query so I don't have to load all the detail into Excel? Thanks, Keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query on Group field in Pivot Table | New Users to Excel | |||
Range.Group Method - group rows, not columns | Excel Programming | |||
Group Dates from SQL Query | Excel Discussion (Misc queries) | |||
Uisng an option group query | New Users to Excel | |||
How do I group worksheets (Lotus 123 function is "Sheet>Group Shee | Excel Worksheet Functions |