Calculating Totals

[shoggy]

Hi,

Wonder could anyone help me…haven’t used Access for a while and I’m a little rusty I am trying to run a query where I need to calculate the total amount of money received between two dates.

My field are DateReceived and FeeAmount. For example I need to calculate the total amount of fees received between 01/01/2007 & 01/04/2007.

Any help appreciated

 

[KenHigg]

How / where are you going to use this value?

 

[shoggy]

I am going to store this in a field called feetotal

 

[namliam]

You shouldnt store calculated values in a table, except for VERY rare exceptions.

 

[shoggy]

If you can tell me how to go about it then that would be of great help!!

 

[namliam]

I am not going to tell you how to do something you should not be doing!

Why would you want to store something which you can calculate at any time you need it? You shouldnt want to do that!
This is just about Rule #1 in database design !

 

[shoggy]

I will not store it then. I just need to know how to calcuate the total amount and I can run it each time!!

 

[KenHigg]

You can you a dsum(). - ?

 

[namliam]

Make a group by query and use the sum...

Select sum(FeeAmount)
from table
where DateReceived between #01/01/2007# and #01/04/2007#

Should do the trick

 

[KenHigg]

dsum("[FeeAmount]", "MyTableName", "[DateReceived] between #1/1/2007# and #1/31/2007#")

 

[Rich]

A parameter Totals query is much more efficient than crappy old DSum