Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 1st, 2004, 04:19 PM
Registered User
 
Join Date: Apr 2004
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Accounting Database

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!

  #2 (permalink)  
Old April 1st, 2004, 07:39 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Melbourne, Vic, Australia.
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #3 (permalink)  
Old April 2nd, 2004, 12:41 PM
Registered User
 
Join Date: Apr 2004
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

  #4 (permalink)  
Old April 2nd, 2004, 12:46 PM
Registered User
 
Join Date: Apr 2004
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #5 (permalink)  
Old April 4th, 2004, 05:35 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Melbourne, Vic, Australia.
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cell Accounting Format jcarey00 Excel VBA 1 September 28th, 2007 04:47 AM
Accounting application tsimsha ASP.NET 1.0 and 1.1 Basics 1 March 27th, 2006 11:59 PM
Accounting receivable aging mustafayildirim@msn.com Access 1 April 25th, 2005 06:54 AM
Accounting System fahimtufail C# 1 April 9th, 2005 05:23 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.