Summing multiple fields in a query

[cdiffell]

Hello,
I've got a query that I want to use to perform a rather complex calculation on the numerical results in 12 fields. As part of it, I need to be able to sum certain of the fields together. I know I can use the following expression:

[SumOfFeb]+[SumOfMar]+[SumOfApr] to give me the sum of those 3 fields.

But what I really want is to be able to provide just the FIRST and LAST fields and have the expression automatically sum all the intervening fields. So, in the above example, I want something like [SumOfFeb]:[SumOfApr] that would add together all 3 fields by only stating the first and last field.

Of course, this will only work if the fields are in a certain order, which they are. Can this be done?

Thanks,
Caleb

 

[Joe4]

Sounds like you are trying to apply "Excel thinking" to Access. Where in Excel, you can designate continuous ranges, I don't believe Access will act that way. I don't believe consecutive fields can be treated like continuous ranges.

There are a few options. You may be able to write a UDF in VBA do what you want, but it may get complicated.

Another option would be to re-design the structure of your tables (which you may want to do, because it sounds like your database may be violating the first rule of normalization). Instead of having multiple month fields in your main table, create a satellite table that has three fields:

ID
Date
Amount

You would then link this table to your main table, and then you would have more flexibility to perform different calculations in a query. You may want to check out CrossTab queries in the Access help, as they may come into play also.

 

[cdiffell]

Good catch - the tables do defy normalization rules. Unfortunately, the database is used to enter budget information on a month-by-month basis, and the users (who can actually go into tables to change info) find it easier to see if they have an "excel" view whereby they can see each month in a column *as they are making their changes*. So using the ID/Month/Amount idea, while proper normalization technique, has been deep-sixed by the users because it's too hard to see the "year at a glance".

Believe me, I fought that battle already and lost, so now I'm just trying to do what I can with what's left

But the upshot is that you can't designate contiguous fields in Access, and that's what I was thinking but needed confirmation.

Thanks
Caleb

 

[Joe4]

But the upshot is that you can't designate contiguous fields in Access, and that's what I was thinking but needed confirmation.

Not without VBA (I believe)!

 

[PaulF]

Another alternative: After the table is linked (with the months in the columns for ease of input) run a query in access to convert the linked table to a more normalized table as described above.

This query would be a series of 12 simple statements unioned together. It's a good bridge between practical needs and good database design, and does not involve VBA.