Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-14-2019, 05:11 AM   #1
Georgenetwork
Newly Registered User
 
Join Date: Nov 2019
Posts: 21
Thanks: 11
Thanked 0 Times in 0 Posts
Georgenetwork is on a distinguished road
Access Help!

Access Help!

Here are my fields for the subform
Table2
EmployeeID
Firstname
LastName
Date of Birth

Table2
ID
EmployeeID
LeaveType (lookup) Annual Leave, Sick Leave
StartDate
EndDate

Annual Leave limit 20days
Sick Leave Limit 10days

The leave windows start from january and ends December. The leave process is flexible to the extend Employee John can take 5 days
of leave in January, then take 10 in September and than 5 in December. How do i sum it up? How do I prevent such employee from taking more than the 20 days when he take his
leave in pieces?
How do i limit Annual Leave to not more than 20 working days and Sick Leave 10 days

Please help

Georgenetwork is offline   Reply With Quote
Old 11-14-2019, 06:25 AM   #2
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,773
Thanks: 93
Thanked 1,729 Times in 1,600 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Access Help!

There are various reasons that people tell you to avoid LOOKUP fields. You have one. Down the road it will bite you on the butt. In fact, for the simple query I will show you, it already has.

You also described two tables named Table2. I'm going to assume that the first table is really Table1.

OK, to find how much leave a person is taking in a given year, you might need a query similar to this:

Code:
SELECT SUM([EndDate]-[StartDate]+1) AS Days, LeaveType, Firstname, Lastname
FROM Table1 INNER JOIN Table2 ON Table1.EmployeeID = Table2.EmployeeID
WHERE StartDate > ( Date() - DatePart( "y", Date() ) ) 
GROUP BY LeaveType ;
The theory of this is you will get 0, 1, or 2 entries. Zero for people who have not taken any leave at all, one for folks who took only one kind of leave, and two for folks who took both kinds of leave. The WHERE clause uses DatePart with "y" to compute something called Julian date, the day of the year. It uses the ">" sign because that particular computation generates Dec. 31 of the previous year.

Note also that if you wanted to look at last year's leave totals, life gets more complex as you then need a more complex method of limiting dates.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Georgenetwork (11-14-2019)
Old 11-14-2019, 06:52 AM   #3
Georgenetwork
Newly Registered User
 
Join Date: Nov 2019
Posts: 21
Thanks: 11
Thanked 0 Times in 0 Posts
Georgenetwork is on a distinguished road
Re: Access Help!

Thanks. Additionally, We only want to calculate workdays between the leavestart and leaveend


Quote:
Originally Posted by The_Doc_Man View Post
There are various reasons that people tell you to avoid LOOKUP fields. You have one. Down the road it will bite you on the butt. In fact, for the simple query I will show you, it already has.

You also described two tables named Table2. I'm going to assume that the first table is really Table1.

OK, to find how much leave a person is taking in a given year, you might need a query similar to this:

Code:
SELECT SUM([EndDate]-[StartDate]+1) AS Days, LeaveType, Firstname, Lastname
FROM Table1 INNER JOIN Table2 ON Table1.EmployeeID = Table2.EmployeeID
WHERE StartDate > ( Date() - DatePart( "y", Date() ) ) 
GROUP BY LeaveType ;
The theory of this is you will get 0, 1, or 2 entries. Zero for people who have not taken any leave at all, one for folks who took only one kind of leave, and two for folks who took both kinds of leave. The WHERE clause uses DatePart with "y" to compute something called Julian date, the day of the year. It uses the ">" sign because that particular computation generates Dec. 31 of the previous year.

Note also that if you wanted to look at last year's leave totals, life gets more complex as you then need a more complex method of limiting dates.

Georgenetwork is offline   Reply With Quote
Old 11-14-2019, 09:39 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,461 Times in 1,442 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Access Help!

Quote:
Originally Posted by Georgenetwork View Post
Thanks. Additionally, We only want to calculate workdays between the leavestart and leaveend
Calculating "workdays" will probably require a separate function like the ones available here.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Access Learner: How to create a multiuser form in MS Access For 'MS Access DB' Danyal Forms 1 12-04-2013 03:13 AM
Upgrading Access Database from Access 2003 to Access 2010 ladyfrankie General 1 03-31-2013 01:19 AM




All times are GMT -8. The time now is 12:56 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World