Help with Report Queries

[Guest]

I know this has to be possible to do, but I can't figure out how. Please help!

I have to put together an employee hours report:

- Along the top of the page will list each month.
- Along the side of the page will list the different types of hours an
employee can report. There can be multiple items within each type (i.e.
OTHER can include Personal Time, Holiday Time, etc.).
- I need to list each individual line and then sum the total hours for each
type.

How do I do this without creating a separate query for each month. I don't
want to have to create a new query each month. I need to run this report by
employee.

Thanks for your ideas!!

 

[Guest]

Hi Heather,

Create your report and have the query return records that fall between a
specific month for the employee. You might like to use a prompt to get the
date range, or (my preferred method) use a control on a form, and also get
the EmployeeID.

In the report header put the Employee's details. Have a Grouping on Type of
Hours, with the heading for Type of Hours in the Group Header, then in the
details section, the line items.

You can then sum the hours field in the group footer, and if you like, sum
them in the report footer also to get overall hours for the month.

Hope that helps.

Damian.

 

[Guest]

Damian,

I did what you said -- the report is starting to look like I need it to.
Now I'm trying to figure out the date thing. I need it to show every time
cycle for year to date. I'm going to list those in the header. Some months
a person may charge their time against one project, and other months they may
use a totally different type. How do I create a space under each month that
may or may not have a number in -- it just depends on what they charged that
month. I have a cycle date in my table so I can do a query based on that.
Here's an example:

Employee X
Jan Feb Mar Apr ....
OTHER
Personal 30 24 0 0
Vacation 0 16 8 0
ADMIN
Data Entry 60 30 80 88
Marketing 5 0 5 5

TOTAL 95 70 93 93

 

[Guest]

Hi again Heather,

If you always want all types to show, regardless of whether they had time
charged against them or not, add the Types table into your query, and left
join it to the EmployeeHours table, eg:

select HourType, EmployeeID, HoursWorked from tblHourType left join
tblEmployeeHours on tblHourType.HourTypeID = tblEmployeeHours.HourTypeID

Remember to include the rest of your fields... then for the Hours Worked,
you will need to use nz([HoursWorked], 0) to remove Nulls and replace them
with zero's.

Have I explained that clearly enough?

Damian.