SUM 15+ fields based on Date & Location

  • Thread starter Thread starter Vince
  • Start date Start date
V

Vince

Hello,

I have a query built that caculates cashout process. I
have over 10 fields I created calculations with e.g. Total
5 Dollar Bills: [5Bill]*5.00

I would like to know how I can now add all these fields
together to get 1 final summary of all the cashiers?

Thus I would need a grand total of all 5 dollars, 10
dollars, 20's, 50's, 100's, coins etc...

I noticed that haveing more than 2 fields will not work
when using the summation????

Please assit...I can visualize this working but can't make
it work....I want to group the records with the same date
and then all them all?!???!?!?!

PLEASE AND THANK YOU!!!

Vince
 
You'll need to visualize restructuring your tables. Access doesn't work
well with spreadsheets.

tblMoney
----------
MoneyID
MoneyDate
MoneyDenomination (e.g. 1, 2, 5, 10, ...)
MoneyNumber (Number of Bills of that Denomination)

Once the data is in a structure such as this, group the data in a query
becomes very simple.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Vince said:
Hello,

I have a query built that caculates cashout process. I
have over 10 fields I created calculations with e.g. Total
5 Dollar Bills: [5Bill]*5.00

I would like to know how I can now add all these fields
together to get 1 final summary of all the cashiers?

Thus I would need a grand total of all 5 dollars, 10
dollars, 20's, 50's, 100's, coins etc...

I noticed that haveing more than 2 fields will not work
when using the summation????

Please assit...I can visualize this working but can't make
it work....I want to group the records with the same date
and then all them all?!???!?!?!

PLEASE AND THANK YOU!!!

Vince
Click to expand...


SELECT Sum(Nz(W1.Bill_1,0))
+ Sum(Nz(W1.Bill_5,0))
+ Sum(Nz(W1.Bill_10,0))
+ etc.
FROM Whatever As W1

However, in the long run, you'll find yourself regreting having a table
with repeating columns in it.
 
Back
Top