Summarizing Records via ADO

One of Access's strengths is running summary queries that group by a particular field. If you build a summary query in Access and examine the SQL view, you will see that complex queries can be written. Similar SQL can be built in Excel VBA and passed to Access via ADO.

The following code uses a fairly complex query to get a net total by store:

Sub NetTransfers(Style As Variant)

' This builds a table of net open 1 on Styles AI1 Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset transfers

Build the large SQL query

Basic Logic: Get all open Incoming Transfers by store, union with -1* outgoing transfers by store Sum that union by store, and give us min date as well A single call to this macro will replace 60 _ calls to GetTransferIn, GetTransferOut, TransferAge sSQL = "Select Store, Sum(Quantity), Min(mDate) From _ (SELECT ToStore AS Store, Sum(Qty) AS Quantity, _ Min(TDate) AS mDate FROM tblTransfer where Style='" & Style & "' AND Receive=FALSE GROUP BY ToStore " sSQL = sSQL & " Union All SELECT FromStore AS Store, _ Sum(-1*Qty) AS Quantity, Min(TDate) AS mDate _ FROM tblTransfer where Style='" & Style & "' AND _ Sent=FALSE GROUP BY FromStore)" sSQL = sSQL & " Group by Store"

MyConn = "J:\transfers.mdb"

0 0

Post a comment