kruuth
May 3 2012, 07:49 AM
I've got some code I'm trying to get to return a count for but so far I'm not having much luck....here's the code(I used a macro recorder):
CODE
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DRIVER=SQL Server;SERVER=MSDSKUSRSQL\TBLBIGNICK;UID=XXXXXXX;PWD=XXXXXXX;APP=2007 Microsoft Office system;WSID=GGERDS01KSN0901" _
, Destination:=range("$E$1")).QueryTable
.CommandText = Array( _
"SELECT DISTINCT OrderListingFullData.DC, OrderListingFullData.CSTNUM, OrderListingFullData.SHIPCTR" & Chr(13) & _
"" & Chr(10) & "FROM CUSTData.dbo.OrderListingFullData OrderListingFullData" & Chr(13) & "" & Chr(10) & _
"WHERE (OrderListingFullData.DC='" &DC_code & "')" _
, "" & Chr(13) & "" & Chr(10) & "ORDER BY OrderListingFullData.DC")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Orders_query"
.Refresh BackgroundQuery:=False
End With
What this presently returns is the customers in a specific dispatch center and their corresponding ship center. Is there a way that I can have this return a count of orders for each customer? There is a column in the table that I'm not using, OrderListingFullData.ORDERNUM. So far I've tried doing this but keep getting SQL syntax errors.
timbald
May 3 2012, 08:01 AM
Counts of records in a query result is done by an aggregation function in SQL. There is a COUNT() function and you use GROUP BY to help aggregate the data you need.
SELECT CustomerPrimaryKeyID as CustomerID, Count([UniqueOrderNumberID]) as CountOfOrders
FROM TheDataTableOfOrders
GROUP BY CustomerPrimaryKeyID
is the kind of syntax you need.
This gives a list like :
CustomerID | CountOfOrders
13 | 23
14 | 4
56 | 1
Note this syntax will not show you customers who have never placed an order. If you need that then it gets a bit more complex, you need to do a left join between the Customers table and the Orders table. Post again if thats needed and I (or someone else no doubt) will give you an updated example to include customers with zero orders as well.
Is this what you were after?
kruuth
May 3 2012, 08:10 AM
Yes but I'm not sure how to do that in the context of what I'm presently returning.
rabroersma
May 3 2012, 08:43 AM
You might try to see if your version of SQL Server support windowing functions. If it does, you can alter your query to the following:
CODE
SELECT DISTINCT OrderListingFullData.DC, OrderListingFullData.CSTNUM,
OrderListingFullData.SHIPCTR, COUNT(*) OVER( PARTITION BY OrderListingFullData.CSTNUM )
FROM CUSTData.dbo.OrderListingFullData OrderListingFullData
WHERE OrderListingFullData.DC = &DC_code
ORDER BY OrderListingFullData.DC
kruuth
May 3 2012, 08:46 AM
IDK....I tried adding this in there but it gives me a SQL error:
CODE
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DRIVER=SQL Server;SERVER=MSDSKUSRSQL\TBLBIGNICK;UID=XXXXXXX;PWD=XXXXXXX;APP=2007 Microsoft Office system;WSID=GGERDS01KSN0901" _
, Destination:=range("$E$1")).QueryTable
.CommandText = Array( _
"SELECT DISTINCT OrderListingFullData.DC, OrderListingFullData.CSTNUM, COUNT([OrderListingFullData].ORDERNUM) AS [ORDERCount], OrderListingFullData.SHIPCTR" & Chr(13) & _
"" & Chr(10) & "FROM CUSTData.dbo.OrderListingFullData OrderListingFullData" & Chr(13) & "" & Chr(10) & _
"WHERE (OrderListingFullData.DC='" &DC_code & "')" _
, "" & Chr(13) & "" & Chr(10) & "ORDER BY OrderListingFullData.DC")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Orders_query"
.Refresh BackgroundQuery:=False
End With
rabroersma
May 3 2012, 08:50 AM
What does the error message say?
kruuth
May 3 2012, 08:54 AM
I get a 1004 SQL syntax error then when i go to debug the line :
".Refresh BackgroundQuery:=False"
is highlighted. If I comment that line I get no data.
norie
May 3 2012, 09:02 AM
Why not do the count in Excel?
kruuth
May 3 2012, 09:10 AM
If I do that then I'll go over the total number of lines I can have in EXCEL
norie
May 3 2012, 09:13 AM
Does the original query work and the data fit on to an Excel worksheet?
If it does I don't see how using Excel for the count wouldn't work - it wouldn't increase the no of rows.
kruuth
May 3 2012, 09:18 AM
It does, the original query also included the orders so I wound up with each order with the cust number, etc. Way over the limit of excel.
norie
May 3 2012, 09:25 AM
So the first code you posted, even though it returns more records thatn Excel can handle?
kruuth
May 3 2012, 10:28 AM
Correct. It returns more b/c some customers have ridiculous numbers of orders.
norie
May 3 2012, 11:40 AM
So if it returns more rows than Excel can handle, how can it work?
kruuth
May 3 2012, 06:35 PM
If I return each and every order number then it can't handle it. However, if I return the count of orders per customer then I clock in at about 40k lines.
norie
May 3 2012, 06:38 PM
Kruuth
Try using a pivot table with the SQL Server query as the external data source.
kruuth
May 4 2012, 06:59 AM
Thanks Norie, I've not worked too much with pivots this is perfect.
norie
May 4 2012, 10:23 AM
No problem.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please
click here.