Calculate leave time in a query

[BrookieOU]

This seems pretty simple, but I cannot figure it out or wrap my head around
it (I'm learning Access as I go and with a lot of help from these forums!).

I have a database for our employees. In the employees table I list all of
the vacation time, sick time and personal time they are alloted for the year
in separate columns (80 vacation, 40 sick and 16 personal). In another table
I am entering their leave time as they take it. The "Leave Table" is set up
as a lookup column for employee name (from the employee table) and a lookup
column for the reason (from typed in values: vacation, sick, personal).

What I am trying to do is run a query that will total the hours taken for
each person for each reason and then subtract those hours from the hours we
give them to give me a balance. I have been able to figure out how to do a
query that will total the hours for each person. What I can't do is figure
out how to do the calculations. I can't just put a calculation in a blank
field in the query because if I do the calculation [Sick Time]-[Hours] it
gives me an incorrect answer because the reason isn't always sick, it could
be vacation or personal. I think I could do it if I did separate queries for
each one -- sick, vacation and personal, but I want it combined on one report.

Does that make sense?

Thanks,
Brooke

 

[Tom Wickerath]

Hi Brooke,

I can't just put a calculation in a blank
field in the query because if I do the calculation [Sick Time]-[Hours] it
gives me an incorrect answer because the reason isn't always sick, it could
be vacation or personal.

It sounds like you might (?) have separate columns defined for sickness,
vacation and personal. With this type of structure, you would need to use a
union query first, to bring the results into two columns: LeaveType and
LeaveHoursTaken. You might want to consider redesigning your table if you
currently have a column dedicated for each type of leave, converting it to a
two column design for LeaveType and LeaveHoursTaken.

Here are some Knowledge Base (KB) articles that may help you with Date /
Time calculations. Disregard references to a particular version of Access in
the titles:

How to Calculate Daily Hours Based on Clock In/Clock Out Times
http://support.microsoft.com/?id=237958

Using dates and times in Access
http://office.microsoft.com/en-us/access/HA010546621033.aspx

On time and how much has elapsed
http://office.microsoft.com/en-us/access/HA011102181033.aspx

How to determine if a date falls on a weekend or a on holiday
http://support.microsoft.com/?id=290152

Non-Microsoft sites:
Calculate Number of Working Days
http://www.mvps.org/access/datetime/date0006.htm

Doing WorkDay Math in VBA
http://www.mvps.org/access/datetime/date0012.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

This seems pretty simple, but I cannot figure it out or wrap my head around
it (I'm learning Access as I go and with a lot of help from these forums!).

I have a database for our employees. In the employees table I list all of
the vacation time, sick time and personal time they are alloted for the year
in separate columns (80 vacation, 40 sick and 16 personal). In another table
I am entering their leave time as they take it. The "Leave Table" is set up
as a lookup column for employee name (from the employee table) and a lookup
column for the reason (from typed in values: vacation, sick, personal).

What I am trying to do is run a query that will total the hours taken for
each person for each reason and then subtract those hours from the hours we
give them to give me a balance. I have been able to figure out how to do a
query that will total the hours for each person. What I can't do is figure
out how to do the calculations. I can't just put a calculation in a blank
field in the query because if I do the calculation [Sick Time]-[Hours] it
gives me an incorrect answer because the reason isn't always sick, it could
be vacation or personal. I think I could do it if I did separate queries for
each one -- sick, vacation and personal, but I want it combined on one report.

Does that make sense?

Thanks,
Brooke

 

[Michael Gramelspacher]

Hi Brooke,

I can't just put a calculation in a blank
field in the query because if I do the calculation [Sick Time]-[Hours] it
gives me an incorrect answer because the reason isn't always sick, it could
be vacation or personal.

It sounds like you might (?) have separate columns defined for sickness,
vacation and personal. With this type of structure, you would need to use a
union query first, to bring the results into two columns: LeaveType and
LeaveHoursTaken. You might want to consider redesigning your table if you
currently have a column dedicated for each type of leave, converting it to a
two column design for LeaveType and LeaveHoursTaken.

Here are some Knowledge Base (KB) articles that may help you with Date /
Time calculations. Disregard references to a particular version of Access in
the titles:

How to Calculate Daily Hours Based on Clock In/Clock Out Times
http://support.microsoft.com/?id=237958

Using dates and times in Access
http://office.microsoft.com/en-us/access/HA010546621033.aspx

On time and how much has elapsed
http://office.microsoft.com/en-us/access/HA011102181033.aspx

How to determine if a date falls on a weekend or a on holiday
http://support.microsoft.com/?id=290152

Non-Microsoft sites:
Calculate Number of Working Days
http://www.mvps.org/access/datetime/date0006.htm

Doing WorkDay Math in VBA
http://www.mvps.org/access/datetime/date0012.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

This seems pretty simple, but I cannot figure it out or wrap my head around
it (I'm learning Access as I go and with a lot of help from these forums!).

I have a database for our employees. In the employees table I list all of
the vacation time, sick time and personal time they are alloted for the year
in separate columns (80 vacation, 40 sick and 16 personal). In another table
I am entering their leave time as they take it. The "Leave Table" is set up
as a lookup column for employee name (from the employee table) and a lookup
column for the reason (from typed in values: vacation, sick, personal).

What I am trying to do is run a query that will total the hours taken for
each person for each reason and then subtract those hours from the hours we
give them to give me a balance. I have been able to figure out how to do a
query that will total the hours for each person. What I can't do is figure
out how to do the calculations. I can't just put a calculation in a blank
field in the query because if I do the calculation [Sick Time]-[Hours] it
gives me an incorrect answer because the reason isn't always sick, it could
be vacation or personal. I think I could do it if I did separate queries for
each one -- sick, vacation and personal, but I want it combined on one report.

Does that make sense?

Thanks,
Brooke

In addition to Tom's advice here is a simple sample I made which might help. Just a few tables and
queries. http://www.psci.net/gramelsp/temp/db2.zip

I can see a database to handle leave becoming quite complicated.

 

[BrookieOU]

Michael,

I was looking at your database and LOVE IT! Thank you for that. One quick
question though. If I wanted to create a form so that we could enter their
leave times available when we hire them and enter their new hire information,
what would I base that on? Would I base it on the tblAllowedLeave and then
just enter three different entries, one for each type of leave?

Hi Brooke,

I can't just put a calculation in a blank
field in the query because if I do the calculation [Sick Time]-[Hours] it
gives me an incorrect answer because the reason isn't always sick, it could
be vacation or personal.

It sounds like you might (?) have separate columns defined for sickness,
vacation and personal. With this type of structure, you would need to use a
union query first, to bring the results into two columns: LeaveType and
LeaveHoursTaken. You might want to consider redesigning your table if you
currently have a column dedicated for each type of leave, converting it to a
two column design for LeaveType and LeaveHoursTaken.

Here are some Knowledge Base (KB) articles that may help you with Date /
Time calculations. Disregard references to a particular version of Access in
the titles:

How to Calculate Daily Hours Based on Clock In/Clock Out Times
http://support.microsoft.com/?id=237958

Using dates and times in Access
http://office.microsoft.com/en-us/access/HA010546621033.aspx

On time and how much has elapsed
http://office.microsoft.com/en-us/access/HA011102181033.aspx

How to determine if a date falls on a weekend or a on holiday
http://support.microsoft.com/?id=290152

Non-Microsoft sites:
Calculate Number of Working Days
http://www.mvps.org/access/datetime/date0006.htm

Doing WorkDay Math in VBA
http://www.mvps.org/access/datetime/date0012.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

This seems pretty simple, but I cannot figure it out or wrap my head around
it (I'm learning Access as I go and with a lot of help from these forums!).

I have a database for our employees. In the employees table I list all of
the vacation time, sick time and personal time they are alloted for the year
in separate columns (80 vacation, 40 sick and 16 personal). In another table
I am entering their leave time as they take it. The "Leave Table" is set up
as a lookup column for employee name (from the employee table) and a lookup
column for the reason (from typed in values: vacation, sick, personal).

What I am trying to do is run a query that will total the hours taken for
each person for each reason and then subtract those hours from the hours we
give them to give me a balance. I have been able to figure out how to do a
query that will total the hours for each person. What I can't do is figure
out how to do the calculations. I can't just put a calculation in a blank
field in the query because if I do the calculation [Sick Time]-[Hours] it
gives me an incorrect answer because the reason isn't always sick, it could
be vacation or personal. I think I could do it if I did separate queries for
each one -- sick, vacation and personal, but I want it combined on one report.

Does that make sense?

Thanks,
Brooke

In addition to Tom's advice here is a simple sample I made which might help. Just a few tables and
queries. http://www.psci.net/gramelsp/temp/db2.zip

I can see a database to handle leave becoming quite complicated.

 

[Michael Gramelspacher]

Michael,

I was looking at your database and LOVE IT! Thank you for that. One quick
question though. If I wanted to create a form so that we could enter their
leave times available when we hire them and enter their new hire information,
what would I base that on? Would I base it on the tblAllowedLeave and then
just enter three different entries, one for each type of leave?

Hi Brooke,

I can't just put a calculation in a blank
field in the query because if I do the calculation [Sick Time]-[Hours] it
gives me an incorrect answer because the reason isn't always sick, it could
be vacation or personal.

It sounds like you might (?) have separate columns defined for sickness,
vacation and personal. With this type of structure, you would need to use a
union query first, to bring the results into two columns: LeaveType and
LeaveHoursTaken. You might want to consider redesigning your table if you
currently have a column dedicated for each type of leave, converting it to a
two column design for LeaveType and LeaveHoursTaken.

Here are some Knowledge Base (KB) articles that may help you with Date /
Time calculations. Disregard references to a particular version of Access in
the titles:

How to Calculate Daily Hours Based on Clock In/Clock Out Times
http://support.microsoft.com/?id=237958

Using dates and times in Access
http://office.microsoft.com/en-us/access/HA010546621033.aspx

On time and how much has elapsed
http://office.microsoft.com/en-us/access/HA011102181033.aspx

How to determine if a date falls on a weekend or a on holiday
http://support.microsoft.com/?id=290152

Non-Microsoft sites:
Calculate Number of Working Days
http://www.mvps.org/access/datetime/date0006.htm

Doing WorkDay Math in VBA
http://www.mvps.org/access/datetime/date0012.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

This seems pretty simple, but I cannot figure it out or wrap my head around
it (I'm learning Access as I go and with a lot of help from these forums!).

I have a database for our employees. In the employees table I list all of
the vacation time, sick time and personal time they are alloted for the year
in separate columns (80 vacation, 40 sick and 16 personal). In another table
I am entering their leave time as they take it. The "Leave Table" is set up
as a lookup column for employee name (from the employee table) and a lookup
column for the reason (from typed in values: vacation, sick, personal).

What I am trying to do is run a query that will total the hours taken for
each person for each reason and then subtract those hours from the hours we
give them to give me a balance. I have been able to figure out how to do a
query that will total the hours for each person. What I can't do is figure
out how to do the calculations. I can't just put a calculation in a blank
field in the query because if I do the calculation [Sick Time]-[Hours] it
gives me an incorrect answer because the reason isn't always sick, it could
be vacation or personal. I think I could do it if I did separate queries for
each one -- sick, vacation and personal, but I want it combined on one report.

Does that make sense?

Thanks,
Brooke

In addition to Tom's advice here is a simple sample I made which might help. Just a few tables and
queries. http://www.psci.net/gramelsp/temp/db2.zip

I can see a database to handle leave becoming quite complicated.

I added a field HireDate to the Employees table.

Build two forms.
Employees Form
EmployeeAllowedLeave Form

Open the Employees Form in design view and drag the EmployeesAllowedLeave Form onto it. They will
be linked on EmployeeID.

In the AfterInsert Event of Employees Form put this.

Private Sub Form_AfterInsert()

Dim db As DAO.Database
Dim s As String

Set db = DBEngine(0)(0)

s = "INSERT INTO EmployeeAllowedLeave " & _
"(EmployeeID, LeaveType, StartDate) " & _
"SELECT Employees.EmployeeID, LeaveTypes.LeaveType, " & _
"Employees.HireDate AS StartDate " & _
"FROM Employees, LeaveTypes " & _
"WHERE Employees.EmployeeID=" & Me.EmployeeID & ";"

MsgBox s

db.Execute s, dbFailOnError

Me.EmployeeAllowedLeave_Form.Requery

Set db = Nothing

End Sub

It will automatically create the entries in the EmployeeAllowedLeave table and show them in the
subform when a new emplooe is added.