Summing multiple fields

[gator]

I need to SUM multiple field values in a row. I know how to set it up in the
Query design using a calculated field and I realize Excel does this. The
reason I want to do it in Access because it seems to have more automation,
whereas if I build the query, I can run it for the result, opposed to Excel
where more steps might need to be done for the same result. Are there any
query wizards in 2003 that automatically calculate multiple field values, or
am I stuck by doing it long-hand by setting up the query design calculated
field by adding each column/field heading to the field name.....or by setting
up SQL?

 

[Jeff Boyce]

It sounds like you are aware that the data structure you have (in Access) is
more suitable for a spreadsheet than a relational database.

That said, it wouldn't make much sense for Access to have a built-in wizard
to help you do something (sum across the row) with data organized in a way
for which it isn't optimized to work. Yes, YOU can do it, but are you sure
you want to?

It would be a little like trying to drive nails with a chainsaw ... doable,
yes. Good idea, not particularly!

What is it about your data that makes it unsuitable for normalizing into a
data structure that Access is optimized for?

What is it about your situation that makes it unsuitable for using Excel as
it was designed?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Jerry Whittle]

You are stuck doing it the hard way.

One reason that Access doesn't have tools to do this automatically is that
such actions often show bad table design. If you have data set up something
like below and need to add up the year's worth of figures, your table
structure isn't correct.

Jan09 Feb09 Mar09 and so on.
111 123 321

It should look something like this:

SalesDate SalesTotal
1/1/2009 111
2/1/2009 123
3/1/2009 321
and so on.

Then a simple Totals query could add up the year's worth of data.