Sum like values in an Array

Question

Thanks for taking the time to read my question.

I'm wondering if there is a way to total up values in an array like you can with a SELECT statement. Group on one column, sum on another. Or is the only way to loop through the array looking for matches and totaling them up?

Thanks

Answer 1

Assuming you’re doing this in VBA there are several ways to go:

1. You can INSERT the array data into a temporary table, and go about your Group process.
2. Staying in VBA, I would sort the array first. Use a new array as the totals array. Summing in a loop, use the change of value of the left column to trigger entries in the new array of the previous sum, and the restart of summing.
3. Thinking about 2., I’d go back to 1, since all the machinery is already there.

---

peter n roth - http://PNR1.com, Maybe some useful stuff

Answer 2

In database design - one generally can (and should) avoid loop code. It doesn't scale to large data sets - as it becomes slow.

The Aggregate query type can certainly group on one column and sum on another.  If you are not familiar with this feature be sure to check out the Sigma symbol (big E) in the ribbon of query design view.