Really, Really Need Help Tracking Employee Attendance

[BrookieOU]

I am trying to track employee attendance. Currently I have:

Employee Table:
Autonumber
Name
Vacation Available
Sick Available
Personal Time Available

Attendance Table:
Autonumber
Name (Look up from Employees Table)
Date
Hours Taken
Reason (Look up from list -- vacation, sick, personal)

What I want to do is to be able to calculate how much leave time they have
remaining and print it in a report. I have been able to do a query where I
use the "if function" to tell me if the reason is "vacation" to subtract
vacation available from Sum of Hours taken to give me a total. The problem
I'm having is I have to do 3 different queries...one for vacation, one for
sick, one for personal. I want to be able to print a report that will show
each employee, total numbers of hours taken per reason and total number of
hours they have left (all in one report). Any ideas?

Thanks,
Brooke

 

[Sheila D]

In your query you should be able to put 3 seperate calculated fields ie
IF vacation
IF personal
IF sick
Then you can base your report on the query.

Sheila

 

[John Spencer]

In theory, you could do that all in one query.
SELECT Employee.[Name]
, First([Vacation Available])
- Nz(SUM(IIF(Reason='Vacation',[hours Taken],0),0) as VRemaining
, First([Sick Available])
- Nz(SUM(IIF(Reason='Sick',[hours Taken],0),0) as SRemaining
, First([Personal Time Available])
- Nz(SUM(IIF(Reason='Personal',[hours Taken],0),0) as PRemaining

FROM Employee LEFT JOIN Attendance
ON Employee.[Name] = Attendance.[Name]
GROUP BY Employee.Name


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[BrookieOU]

Where do I put that code? Sorry, I'm new at the programming part and am
learning as I go.

In theory, you could do that all in one query.
SELECT Employee.[Name]
, First([Vacation Available])
- Nz(SUM(IIF(Reason='Vacation',[hours Taken],0),0) as VRemaining
, First([Sick Available])
- Nz(SUM(IIF(Reason='Sick',[hours Taken],0),0) as SRemaining
, First([Personal Time Available])
- Nz(SUM(IIF(Reason='Personal',[hours Taken],0),0) as PRemaining

FROM Employee LEFT JOIN Attendance
ON Employee.[Name] = Attendance.[Name]
GROUP BY Employee.Name


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I am trying to track employee attendance. Currently I have:

Employee Table:
Autonumber
Name
Vacation Available
Sick Available
Personal Time Available

Attendance Table:
Autonumber
Name (Look up from Employees Table)
Date
Hours Taken
Reason (Look up from list -- vacation, sick, personal)

What I want to do is to be able to calculate how much leave time they have
remaining and print it in a report. I have been able to do a query where I
use the "if function" to tell me if the reason is "vacation" to subtract
vacation available from Sum of Hours taken to give me a total. The problem
I'm having is I have to do 3 different queries...one for vacation, one for
sick, one for personal. I want to be able to print a report that will show
each employee, total numbers of hours taken per reason and total number of
hours they have left (all in one report). Any ideas?

Thanks,
Brooke

 

[John Spencer]

That is a QUERY. Open a new query, don't add any tables

Copy the code
Select VIEW: SQL from the menu
Paste the code
Fix the field and table names to match your field and table names.

IF any field or table name contains spaces surround the field name or the
table name with square brackets. Replace the reason values with the values
you are storing.

SELECT [Employee].[Name]
, First([Vacation Available])
- Nz(SUM(IIF([Reason]='Vacation',[hours Taken],0),0) as VRemaining
, First([Sick Available])
- Nz(SUM(IIF([Reason]='Sick',[hours Taken],0),0) as SRemaining
, First([Personal Time Available])
- Nz(SUM(IIF([Reason]='Personal',[hours Taken],0),0) as PRemaining
FROM [Employee] LEFT JOIN [Attendance]
ON [Employee].[Name] = [Attendance].[Name]
GROUP BY [Employee].[Name]

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Where do I put that code? Sorry, I'm new at the programming part and am
learning as I go.

In theory, you could do that all in one query.
SELECT Employee.[Name]
, First([Vacation Available])
- Nz(SUM(IIF(Reason='Vacation',[hours Taken],0),0) as VRemaining
, First([Sick Available])
- Nz(SUM(IIF(Reason='Sick',[hours Taken],0),0) as SRemaining
, First([Personal Time Available])
- Nz(SUM(IIF(Reason='Personal',[hours Taken],0),0) as PRemaining

FROM Employee LEFT JOIN Attendance
ON Employee.[Name] = Attendance.[Name]
GROUP BY Employee.Name


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I am trying to track employee attendance. Currently I have:

Employee Table:
Autonumber
Name
Vacation Available
Sick Available
Personal Time Available

Attendance Table:
Autonumber
Name (Look up from Employees Table)
Date
Hours Taken
Reason (Look up from list -- vacation, sick, personal)

What I want to do is to be able to calculate how much leave time they have
remaining and print it in a report. I have been able to do a query where I
use the "if function" to tell me if the reason is "vacation" to subtract
vacation available from Sum of Hours taken to give me a total. The problem
I'm having is I have to do 3 different queries...one for vacation, one for
sick, one for personal. I want to be able to print a report that will show
each employee, total numbers of hours taken per reason and total number of
hours they have left (all in one report). Any ideas?

Thanks,
Brooke

 

[BrookieOU]

John,

It tells me I have a missing operator in the expression. Here is how I did
it with my notes:

SELECT [Employees].[Employee Name]
, First([Vacation Available])
- Nz(SUM(IIF([Reason]='Vacation',[hours Taken],0),0) as VRemaining
, First([Sick Available])
- Nz(SUM(IIF([Reason]='Sick',[hours Taken],0),0) as SRemaining
, First([Personal Available])
- Nz(SUM(IIF([Reason]='Personal',[hours Taken],0),0) as PRemaining


FROM [Employees] LEFT JOIN [Leave Time]
ON [Employees].[Employee Name] = [Leave Time].[Employee Name]
GROUP BY [Employees].[Employee Name]


Could you tell me where I went wrong? The only differences are that my
"Employee" table is labled "Employees" and "Name" is "Employee Name" and
"Personal Time Available" is just "Personal Time"

That is a QUERY. Open a new query, don't add any tables

Copy the code
Select VIEW: SQL from the menu
Paste the code
Fix the field and table names to match your field and table names.

IF any field or table name contains spaces surround the field name or the
table name with square brackets. Replace the reason values with the values
you are storing.

SELECT [Employee].[Name]
, First([Vacation Available])
- Nz(SUM(IIF([Reason]='Vacation',[hours Taken],0),0) as VRemaining
, First([Sick Available])
- Nz(SUM(IIF([Reason]='Sick',[hours Taken],0),0) as SRemaining
, First([Personal Time Available])
- Nz(SUM(IIF([Reason]='Personal',[hours Taken],0),0) as PRemaining
FROM [Employee] LEFT JOIN [Attendance]
ON [Employee].[Name] = [Attendance].[Name]
GROUP BY [Employee].[Name]

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Where do I put that code? Sorry, I'm new at the programming part and am
learning as I go.

In theory, you could do that all in one query.
SELECT Employee.[Name]
, First([Vacation Available])
- Nz(SUM(IIF(Reason='Vacation',[hours Taken],0),0) as VRemaining
, First([Sick Available])
- Nz(SUM(IIF(Reason='Sick',[hours Taken],0),0) as SRemaining
, First([Personal Time Available])
- Nz(SUM(IIF(Reason='Personal',[hours Taken],0),0) as PRemaining

FROM Employee LEFT JOIN Attendance
ON Employee.[Name] = Attendance.[Name]
GROUP BY Employee.Name


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

BrookieOU wrote:
I am trying to track employee attendance. Currently I have:

Employee Table:
Autonumber
Name
Vacation Available
Sick Available
Personal Time Available

Attendance Table:
Autonumber
Name (Look up from Employees Table)
Date
Hours Taken
Reason (Look up from list -- vacation, sick, personal)

What I want to do is to be able to calculate how much leave time they have
remaining and print it in a report. I have been able to do a query where I
use the "if function" to tell me if the reason is "vacation" to subtract
vacation available from Sum of Hours taken to give me a total. The problem
I'm having is I have to do 3 different queries...one for vacation, one for
sick, one for personal. I want to be able to print a report that will show
each employee, total numbers of hours taken per reason and total number of
hours they have left (all in one report). Any ideas?

Thanks,
Brooke

 

[John Spencer]

If the field name is Personal Time then change Personal Available to Personal Time

Also, I note that I missed a closing parentheses in the expressions.
SELECT [Employees].[Employee Name]
, First([Vacation Available])
- Nz(SUM(IIF([Reason]='Vacation',[hours Taken],0)),0) as VRemaining
, First([Sick Available])
- Nz(SUM(IIF([Reason]='Sick',[hours Taken],0)),0) as SRemaining
, First([Personal Time])
- Nz(SUM(IIF([Reason]='Personal',[hours Taken],0)),0) as PRemaining
FROM [Employees] LEFT JOIN [Leave Time]
ON [Employees].[Employee Name] = [Leave Time].[Employee Name]
GROUP BY [Employees].[Employee Name]

You can try some trouble-shooting techniques.
Try breaking the query down into smaller increments:
Does this query work? If so, then you know a calculation is causing the problem.
SELECT [Employees].[Employee Name]
, First([Vacation Available])
, First([Sick Available])
, First([Personal Time])
FROM [Employees] LEFT JOIN [Leave Time]
ON [Employees].[Employee Name] = [Leave Time].[Employee Name]
GROUP BY [Employees].[Employee Name]

So now try adding in ONE of the expressions and see if it works. If so, add
another, if not try playing with the expression by removing the NZ and the IIF
sections. I did that and that was when I noticed that I had unbalanced
parentheses pairs. Gotta have the same number of right and left parens and
then gotta get them in the correct places.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[BrookieOU]

Thank you sooooo much, it worked like a charm. Also, thank you for teaching
me how to do the troubleshooting by breaking it down.

I would like to thank you and everyone else that takes the time to help us
here in the forums. Y'all go above and beyond what people ask of you,
considering you don't have to do it at all. As a "Thank You" I would like to
make a donation in your name to a charity of your choice. Please let me know
what you would like that to be! And once again, thank you sooooo much for
your help. You guys are geniuses!

If the field name is Personal Time then change Personal Available to Personal Time

Also, I note that I missed a closing parentheses in the expressions.
SELECT [Employees].[Employee Name]
, First([Vacation Available])
- Nz(SUM(IIF([Reason]='Vacation',[hours Taken],0)),0) as VRemaining
, First([Sick Available])
- Nz(SUM(IIF([Reason]='Sick',[hours Taken],0)),0) as SRemaining
, First([Personal Time])
- Nz(SUM(IIF([Reason]='Personal',[hours Taken],0)),0) as PRemaining
FROM [Employees] LEFT JOIN [Leave Time]
ON [Employees].[Employee Name] = [Leave Time].[Employee Name]
GROUP BY [Employees].[Employee Name]

You can try some trouble-shooting techniques.
Try breaking the query down into smaller increments:
Does this query work? If so, then you know a calculation is causing the problem.
SELECT [Employees].[Employee Name]
, First([Vacation Available])
, First([Sick Available])
, First([Personal Time])
FROM [Employees] LEFT JOIN [Leave Time]
ON [Employees].[Employee Name] = [Leave Time].[Employee Name]
GROUP BY [Employees].[Employee Name]

So now try adding in ONE of the expressions and see if it works. If so, add
another, if not try playing with the expression by removing the NZ and the IIF
sections. I did that and that was when I noticed that I had unbalanced
parentheses pairs. Gotta have the same number of right and left parens and
then gotta get them in the correct places.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

John,

It tells me I have a missing operator in the expression. Here is how I did
it with my notes:

SELECT [Employees].[Employee Name]
, First([Vacation Available])
- Nz(SUM(IIF([Reason]='Vacation',[hours Taken],0),0) as VRemaining
, First([Sick Available])
- Nz(SUM(IIF([Reason]='Sick',[hours Taken],0),0) as SRemaining
, First([Personal Available])
- Nz(SUM(IIF([Reason]='Personal',[hours Taken],0),0) as PRemaining


FROM [Employees] LEFT JOIN [Leave Time]
ON [Employees].[Employee Name] = [Leave Time].[Employee Name]
GROUP BY [Employees].[Employee Name]


Could you tell me where I went wrong? The only differences are that my
"Employee" table is labled "Employees" and "Name" is "Employee Name" and
"Personal Time Available" is just "Personal Time"

"John Spencer" wrote:

 

[John Spencer]

I'll take it.

Make a contribution to your local Salvation Army.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thank you sooooo much, it worked like a charm. Also, thank you for teaching
me how to do the troubleshooting by breaking it down.

I would like to thank you and everyone else that takes the time to help us
here in the forums. Y'all go above and beyond what people ask of you,
considering you don't have to do it at all. As a "Thank You" I would like to
make a donation in your name to a charity of your choice. Please let me know
what you would like that to be! And once again, thank you sooooo much for
your help. You guys are geniuses!

If the field name is Personal Time then change Personal Available to Personal Time

Also, I note that I missed a closing parentheses in the expressions.
SELECT [Employees].[Employee Name]
, First([Vacation Available])
- Nz(SUM(IIF([Reason]='Vacation',[hours Taken],0)),0) as VRemaining
, First([Sick Available])
- Nz(SUM(IIF([Reason]='Sick',[hours Taken],0)),0) as SRemaining
, First([Personal Time])
- Nz(SUM(IIF([Reason]='Personal',[hours Taken],0)),0) as PRemaining
FROM [Employees] LEFT JOIN [Leave Time]
ON [Employees].[Employee Name] = [Leave Time].[Employee Name]
GROUP BY [Employees].[Employee Name]

You can try some trouble-shooting techniques.
Try breaking the query down into smaller increments:
Does this query work? If so, then you know a calculation is causing the problem.
SELECT [Employees].[Employee Name]
, First([Vacation Available])
, First([Sick Available])
, First([Personal Time])
FROM [Employees] LEFT JOIN [Leave Time]
ON [Employees].[Employee Name] = [Leave Time].[Employee Name]
GROUP BY [Employees].[Employee Name]

So now try adding in ONE of the expressions and see if it works. If so, add
another, if not try playing with the expression by removing the NZ and the IIF
sections. I did that and that was when I noticed that I had unbalanced
parentheses pairs. Gotta have the same number of right and left parens and
then gotta get them in the correct places.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

John,

It tells me I have a missing operator in the expression. Here is how I did
it with my notes:

SELECT [Employees].[Employee Name]
, First([Vacation Available])
- Nz(SUM(IIF([Reason]='Vacation',[hours Taken],0),0) as VRemaining
, First([Sick Available])
- Nz(SUM(IIF([Reason]='Sick',[hours Taken],0),0) as SRemaining
, First([Personal Available])
- Nz(SUM(IIF([Reason]='Personal',[hours Taken],0),0) as PRemaining


FROM [Employees] LEFT JOIN [Leave Time]
ON [Employees].[Employee Name] = [Leave Time].[Employee Name]
GROUP BY [Employees].[Employee Name]


Could you tell me where I went wrong? The only differences are that my
"Employee" table is labled "Employees" and "Name" is "Employee Name" and
"Personal Time Available" is just "Personal Time"

"John Spencer" wrote: