D
David Kinsley
I've been trying to find a solution to my problem for
over a week and am truly frustrated by this. I thank you
in advance for any help/guidance you can provide.
I'm trying to create a database to manage employee time
sheets by day as well as have the ability to create
management reports.
I created a form, where you would select the employee
name, the date and then input how many hours of Reg, OT,
DT, or if they called in Sick, or used Vac, or things of
that nature. If they called in Sick, you input an 8 in
the Sick column and leave all others blank (the reason
for the 8 is that for another report I can sum all the
hours paid as sick).
Now what I'm trying to do is to create a report (similiar
to a crosstab query) that I can give a set of dates as
paramaters (i.e. 2/2/2004 (begining date) 2/8/2004
(ending date)) that now are automatically the column
headers. Now the data under those would be a summary of
what happened to that employee on that day. Ideally I
would like it to either say the number of total hours
worked (i.e. 8) or if no hours were worked display what
did happen (i.e. "Sick").
So my report would look like this and it would
automatically generate the dates based on the paramters I
enter in the begining:
Name 2/2/2004 2/3/2004 2/4/2004 2/5/2004
emp 1 8 Sick 10.5 sick
emp 2 Vac Vac 8 6
I can not use a crosstab query because I don't have an
aggregate function, but could have a number of different
values. I was able to create a query that created the
data I need for the above. Specifically I wrote an if
statement that checked if hrs worked (Reg+Ot+Dt)>0 then
input that number, otherwise it checks if Sick>0, then
input "Sick, otherwise check the Vac and so on and so
on. But I cannot get this data to be output with the by
dates with the dates as column headers in my report.
I am completely stumped on how to accomplish this. If
anyone can help or give guidance I would be very grateful.
Thank you,
David
over a week and am truly frustrated by this. I thank you
in advance for any help/guidance you can provide.
I'm trying to create a database to manage employee time
sheets by day as well as have the ability to create
management reports.
I created a form, where you would select the employee
name, the date and then input how many hours of Reg, OT,
DT, or if they called in Sick, or used Vac, or things of
that nature. If they called in Sick, you input an 8 in
the Sick column and leave all others blank (the reason
for the 8 is that for another report I can sum all the
hours paid as sick).
Now what I'm trying to do is to create a report (similiar
to a crosstab query) that I can give a set of dates as
paramaters (i.e. 2/2/2004 (begining date) 2/8/2004
(ending date)) that now are automatically the column
headers. Now the data under those would be a summary of
what happened to that employee on that day. Ideally I
would like it to either say the number of total hours
worked (i.e. 8) or if no hours were worked display what
did happen (i.e. "Sick").
So my report would look like this and it would
automatically generate the dates based on the paramters I
enter in the begining:
Name 2/2/2004 2/3/2004 2/4/2004 2/5/2004
emp 1 8 Sick 10.5 sick
emp 2 Vac Vac 8 6
I can not use a crosstab query because I don't have an
aggregate function, but could have a number of different
values. I was able to create a query that created the
data I need for the above. Specifically I wrote an if
statement that checked if hrs worked (Reg+Ot+Dt)>0 then
input that number, otherwise it checks if Sick>0, then
input "Sick, otherwise check the Vac and so on and so
on. But I cannot get this data to be output with the by
dates with the dates as column headers in my report.
I am completely stumped on how to accomplish this. If
anyone can help or give guidance I would be very grateful.
Thank you,
David