Access Help! (1 Viewer)

Georgenetwork

Registered User.
Local time
Today, 13:41
Joined
Nov 1, 2019
Messages
21
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
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:41
Joined
Feb 28, 2001
Messages
27,001
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

Registered User.
Local time
Today, 13:41
Joined
Nov 1, 2019
Messages
21
Thanks. Additionally, We only want to calculate workdays between the leavestart and leaveend


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.
 

Isaac

Lifelong Learner
Local time
Today, 13:41
Joined
Mar 14, 2017
Messages
8,738
I agree, I would write a function for this purpose. Even if it could be done with a visually hideous combination of Date functions, it would be hell to look at for anyone coming after you. The benefit of writing a VBA function in these cases is more than for the necessary...it also provides the ability to comment, explain, document & troubleshoot.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:41
Joined
Feb 19, 2002
Messages
42,976
I uploaded a copy of a db with useful date functions including this one. To calculate work days requires a holiday calendar.

To accurately record leave, you don't record ranges. You record one record for each day. If it is an hourly employee, you record the number of hours per day. For salaried employees, your company might allow half days. Once the leave is recorded correctly, summing it is done with a totals query.
 

Attachments

  • UsefulDateFunctions20201124b.zip
    300.2 KB · Views: 85

Users who are viewing this thread

Top Bottom