Sum Multiple Fields in One Record with Other Records as Well

[Guest]

Greetings,

I have a table with multiple amount fields for different General Ledger codes

For example, one record has the following information:

Invoice Number 12345
Amount I $10.00
Account Number 66666
Department Front Desk
Amount II $20.00
Account Number 62354
Department Front Desk
Amount III $30.00
Account Number 66666
Department Admin&General

And another record has the following information:

Invoice ABCDE
Amount I $30.00
Account Number 62230
Department Front Desk

When I run a report, I would like to see the following output:

Front Desk
66666 $40.00
62354 $30.00

Admin&General
66666 $30.00


Essentially, I need it to sum up all amounts with the same Department and
General Ledger Account Number. Any help would be greatly apprciated!

 

[Guest]

Hi Nakia,

Your task will be sooooo much easier if you take the time to normalize your
tables properly. Your design includes repeating groups of fields. You should
spend some time gaining an understanding of database design and normalization
before attempting to build something in Access (or any RDBMS software for
that matter). Here are some links to get you started. Don't underestimate
the importance of gaining a good understanding of database design. Brew a
good pot of tea or coffee and enjoy reading!

http://www.seattleaccess.org/
(See the last download titled "Understanding Normalization" in the Meeting
Downloads page)

<Begin Quote (from page 23 of document)>
"The most important point for you to remember is that you will always
re-introduce data integrity problems when you de-Normalize your structures!
This means that it becomes incumbent upon you or the user to deal with this
issue. Either way, it imposes an unnecessary burden upon the both of you.
De-Normalization is one issue that you'll have to weigh and decide for
yourself whether the perceived benefits are worth the extra effort it will
take to maintain the database properly."
<End Quote>


http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

Also recommended: Find the copy of Northwind.mdb that is probably already
installed on your hard drive. Study the relationships between the various
tables (Tools > Relationships...)


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________