Aggregating Access VBA Array

twoplustwo

Registered User.
Local time
Today, 12:40
Joined
Oct 31, 2007
Messages
507
Hi all, hope you're well.

I have the following data (assume each item is a column in the array) set of about 120K records:

Customer1 A B 120
Customer1 A B 100
Customer1 A C 200
Customer2 A D 150
Customer2 A D 100
Customer2 A E 100

I need to aggregate this up into the following:

Customer1 A B 220
Customer1 A B 100
Customer1 A C 200
Customer2 A D 250
Customer2 A E 100

I need to basically group by the first 3 items and sum the last column - similar to using a query.

Any ideas?

Thanks.
 
Can you not do this in the query and if you then want to use this in a module then you can look at the SQL statement behind the query and then copy and paste it into a module add an sql statement and then run it to where you want the results to go?
 
Hi Trevor,

It's an array of analysed data - it's not stored in the database (i.e. in a table) so no.
 
Why don't you just use VBA to insert the 120,000 records into a table and then run a group-by query on the table? (VBA can do all this pretty fast).

Another option is to use a Dictionary (with a Type-object, i.e.,a Customer obect/type), but I'm not sure it would be much faster (depends on how many different Customers you have).

If you only have 100 Customers, your Dictionary would only have 100 entries, so it might run pretty fast. Actually I can't remember if you can load a Customer Type into a dictionary - you might need to define a Customer Class instead.
 
To be more clear, a Dictionary can be used to simulate a Group-By. Here's some pseduo-code (it's not real syntax) to give you the idea. Suppose you wanted to sum the orders for each customer (so you decide to group by FirstName, LastName

Dim dic as Dictionary
Foreach C as Customer in Customers
Dim key as string
key = C.FirstName & C.LastName
if dic.Contains(key) Then 'this cust already in the dic
totalForThisCust = dic(key)
newTotal = totalForThisCust + 1 'adds the current record
dic(key) = newTotal
Else 'Add this cusotmer to the dictionary, set total to 1
dic.Add Key, 1
End if
Next Customer

At the end, the dic will contain each customer, and the total number of orders for that customer.
 
To be more clear, a Dictionary can be used to simulate a Group-By. Here's some pseduo-code (it's not real syntax) to give you the idea. Suppose you wanted to sum the orders for each customer (so you decide to group by FirstName, LastName

Dim dic as Dictionary
Foreach C as Customer in Customers
Dim key as string
key = C.FirstName & C.LastName
if dic.Contains(key) Then 'this cust already in the dic
totalForThisCust = dic(key)
newTotal = totalForThisCust + 1 'adds the current record
dic(key) = newTotal
Else 'Add this cusotmer to the dictionary, set total to 1
dic.Add Key, 1
End if
Next Customer

At the end, the dic will contain each customer, and the total number of orders for that customer.

Jal I have never seen this done before and would love to see a sample database with it working, as this would resolve so many issues and long winded ways around other problems.

Would you be prepared to upload a working sample?

I copied the extract into a module and it highlighted in Red
Foreach C as Customer in Customers
 
Jal I have never seen this done before and would love to see a sample database with it working, as this would resolve so many issues and long winded ways around other problems.

Would you be prepared to upload a working sample?

I copied the extract into a module and it highlighted in Red

I've done this using .Net dictionaries which are fast because, unlike VBA dictionaries, they are strongly typed. And, as I said, I'm not even sure that VBA dictionaries can hold a Customer Type. On the other hand I'm fairly certain they can hold a Customer Class, so maybe I'll create a sample using a class.

But I doubt it would run much faster (and possibly slower) than using regular tables (due to the lack of strong typing). So it's probably a lot of unecessary work, but maybe I'll upload a sample when i can find the time.
 
I just thought of yet another solution. You can load the data into a disconnected recordset and then call

rs.Sort = "FirstName, LastName"

and then loop through the sorted recordset. Every time the FirstName+LastName combo changes, you've reached a new Customer, and thereby simulates a group By.

In fact the rs probably doesn't have to be disconnected. I've done this with an ADODB recordset connected to a tempTable. I "added" records but didn't actually call Update so the records where never actually inserted - I merely used the recordset to sort the records, and it ran pretty fast, as I recall.

I like this method because you don't have to define a Customer Class placed in a public module - the result is cleaner, shorter code.
 
thats interesting , jal

the concept of a dictionary is a new one to me - dont think I've ever seen it used before
 
Morning all,

Jal,

I tried the first suggestion inserting into a new table - that worked reasonably well/fast (it's not too many columns), so thanks. Might be the easiest way for me to aggregate the data.

I am going to look at the dictionary suggestion today so thanks again :)
 
Morning all,

Jal,

I tried the first suggestion inserting into a new table - that worked reasonably well/fast (it's not too many columns), so thanks. Might be the easiest way for me to aggregate the data.

I am going to look at the dictionary suggestion today so thanks again :)
Thanks for the feedback. Honestly I'm more optimistic about the disconnected recordset approach, versus the dictionary, for reasons stated above. But I'm glad you got the standard table-method working.
 

Users who are viewing this thread

Back
Top Bottom