Problem summing a recordset

[MobiusDick]

Hi Everyone,

I'm having a slight problem summing (almost) an entire recordset:

I have an application that when given a table of customers and their estimated annual electricity consumption will generate a matrix of coefficients that gives a typical consumption profile (i.e. how much electricity they are using in any half hourly period).

The matrix is a table "tblProfileForPeriod" with the fields as follows:

"Profile_Class;Settlement_Date;HH01;HH02.....;HH48"

A profile class will be constant for the date range selected, with 8 profile classes in all. The date range is constant for each profile class.

What I need to do is sum all HH01:HH48 for all the dates within each profile class; however, I have not been able to achieve anywhere near this result without using a ridiculous amount of nested queries and would like to do this using VBA.

Please help.

 

[DrSnuggles]

Is this what you are trying to do? . .

For each Customer
For each Profile_Class
Add all the values in fields starting with the letters 'HH'
Update a temporary table/audit table
Next Profile_Class
Next Customer

 

[MobiusDick]

Hi DrSnuggles,

Thanks for the reply-

Yes that is what I am trying to do.

Hope you can help.

 

[neileg]

Your design is wrong. You shouldn't have 48 fields in a record, you should have 48 records in a related table. Access is a relational database, not a spreadsheet.

Why do you want to sum this in a query or VBA? It would be pretty easy in a report. Having said that, I don't see why an aggregate query wouldn't give you what you want.

 

[MobiusDick]

Hi Neil,

Unfortunately the nature of the calculations I have to perform and the amount of data required to calculate anything where electricity is concerned make it almost impossible to seperate into separate tables as you suggest.

To explain- if you have 8 profile classes all requiring a different coefficient for each half hour of each date for a years worth of data whichever way you arrange it you end up, at minimum, with a matrix of 48 * 365 or 365 * 48- otherwise you end up with well over 1000 tables. If I treated all of the profiles in one table I would end up with an (8*365) * 48 matrix.

As to why I wanted to do it in VBA- I would like it to be tidier and more efficient than I can make it using aggregate queries in which I have to sum the columns and the rows.

The results of this addition are not the final stage, more calculations have yet to be performed- but if you have a way to use a report to achieve what I'm after and then use the data from that report for further calculations I would like to consider it.

Many thanks

 

[neileg]

That's not what I meant. I wasn't suggesting multiple tables, I was meaning one additional table. The number of records in a table is not an issue. 8*48*365=140,160 records. You would then perform an aggregate query on this one table, grouping as appropriate. Efficiency comes from design, not a choice between VBA and SQL.

I do recognise that you are using half hourly billing data.

 

[MobiusDick]

Hi Neil,

My apologies- its not that often people are familiar with HH data.

Could you explain what you mean by one additional table- I am interested to understand.

Cheers,
Mark

 

[neileg]

I would expect the tables to look something like this:

tblProfileForPeriod
ProfileID autonumber primary key
Profile_Class
Settlement_Date

tblHalfHours
HHID autonumber primary key
ProfileID number foreign key link to tblProfileForPeriod
HalfHour number (values would be 1 to 48)
Coefficient number

 

[John_W]

I've often worked with hourly/daily etc data in this way, and sometimes i've used Neil's method and sometimes Mark's. I appreciate Neil's is more proper, and follows normalising rules, but Mark's structure makes for easier data entry as you can simply present a form based on the table. And since there are a finite number of half-hours in a day you know you won't run out of fields. It does make querys a bit more complicated though.

I'd have thought you need a query which has a calculated field equal to the sum of all the HH fields. Then a second query based on this to group and sum in the way you need.

 

[MobiusDick]

Thanks Neil, I'll experiment with the structure.

John_W- you're absolutely right the queries are somewhat more complicated than I expected.

The trouble i've found with trying to sum 48 half hourly periods is that Access will only allow a certain number of characters in any calculated field, typically I only seem to be able to add 28 half hourly periods in one go- which means that, in order to complete the summing, I have to have 2 queries to sum across the rows and another 1 to sum up the SumOfRows column, hence I was looking for a programmatic way of summing a matrix. Whilst perhaps not essential to the overall operation of the database the ability to do this in one step would just be nice.