If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Summing multiple fields
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? |
#2
|
|||
|
|||
Summing multiple fields
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. "gator" wrote in message ... 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? |
#3
|
|||
|
|||
Summing multiple fields
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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "gator" wrote: 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? |
Thread Tools | |
Display Modes | |
|
|