Accounting Database

Lighthouse · April 1st, 2004, 04:19 PM

I am trying to set up a simple accounting database. I would like to know how to have a field automatically calculate the weekly, monthly,and yearly totals based upon what I enter into my daily fields.

Thank YOu!

Steven · April 1st, 2004, 07:39 PM

Without any further information.........
Something along the lines of:

Code:

SELECT DatePart("yyyy", [YourDateField]), Sum([YourMoneyField]) 
FROM YourTable 
GROUP BY DatePart("yyyy", [YourDateField])

would give you the yearly totals.

Something like:

Code:

SELECT DatePart("yyyy", [YourDateField]), DatePart("m", [YourDateField]), Sum([YourMoneyField]) 
FROM YourTable 
GROUP BY DatePart("yyyy", [YourDateField]), DatePart("m", [YourDateField])

would give you the totals for each month

a little more info on where you'd like this shown (Form/Report/Query etc), would probably be helpful

I am a loud man with a very large hat. This means I am in charge

Lighthouse · April 2nd, 2004, 12:41 PM

Thanks for the info. Sorry so sparse on the background info. The daily data will be entered via a form and will want the monthly and yearly recorded on the appropriate table.

Lighthouse · April 2nd, 2004, 12:46 PM

Let me clarify even more. I want to enter the daily totals on a form and have the monthly and yearly automatically calculate on the form as well. As a new record on the form is added then the montly and yearly will recalculate based upon the record date. So if I go back a record then the monthly and yearly will record the values based upon that record date.

Thanks

Steven · April 4th, 2004, 05:35 PM

Sounds to me like you might want to check out the Domain Aggregate functions in the help file (Specifically DSum)

I am a loud man with a very large hat. This means I am in charge