group/sum in ms query

[Keith]

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

 

[Joel]

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