Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access 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
 
Old August 18th, 2005, 10:43 AM
Registered User
 
Join Date: Jul 2003
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to check end of month

I'm creating employee vacation database and want to add 2 days to vacation days at the end of each month. How do I do this? Thanks in advance for your help.
 
Old August 19th, 2005, 01:30 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You need to keep this data in a seperate table, then run an update query at the end of each month. Put a date stamp on each new entry. Have the update query code check to make sure you are not double entering the vacation days by checking the update date month against the current or last month.
Then report on vacation days with another query that totals them all up.
You don't want to have one figure that you are changing every month, like this:

mmcdonal 2 days (DELETE 2 and replace with next value)
mmcdonal 4 days (DELETE 4 and replace with next value)
mmcdonal 6 days (etc)
etc.

You want to do this:
April 2005 mmcdonal 2 days
May 2005 mmcdonal 2 days
June 2005 mmcdonal 2 days

Then your query/report/form will show a TOTAL of 6 days.

HTH

mmcdonal
 
Old August 19th, 2005, 01:30 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Sorry, not an update query but an APPEND query.

mmcdonal
 
Old August 19th, 2005, 01:32 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

If you use this method then it is easy to remove spent days (and keep a record of them) using another table and another query to total and subtract. (Total accumulated vacation days in one table, total used vacation days in another or same table with a used switch, then subtract used from accumulated.)


mmcdonal
 
Old August 22nd, 2005, 09:41 AM
Registered User
 
Join Date: Jul 2003
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by mmcdonal
 You need to keep this data in a seperate table, then run an update query at the end of each month. Put a date stamp on each new entry. Have the update query code check to make sure you are not double entering the vacation days by checking the update date month against the current or last month.
Then report on vacation days with another query that totals them all up.
You don't want to have one figure that you are changing every month, like this:

mmcdonal 2 days (DELETE 2 and replace with next value)
mmcdonal 4 days (DELETE 4 and replace with next value)
mmcdonal 6 days (etc)
etc.

You want to do this:
April 2005 mmcdonal 2 days
May 2005 mmcdonal 2 days
June 2005 mmcdonal 2 days

Then your query/report/form will show a TOTAL of 6 days.

HTH

mmcdonal

Thanks mmcdonal for your help. I will let you know the outcome.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting month name to month number kalyanykk SQL Server 2005 7 August 19th, 2008 10:37 PM
Ms Access front End with Oracle 10g Back End rahul123 Oracle 1 July 9th, 2007 01:03 AM
Oracle back-end MS-Access 2003 client front-end Corey Access 2 February 16th, 2007 08:31 AM
Oracle Back End - MS Access Front End - Multi User ckaliveas Oracle 1 February 1st, 2007 06:00 AM
query Current Month, Month+1, Month+2, Month+3 anterior Access 2 September 24th, 2006 08:25 PM





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