How to group fields and corresponding fields, while calculating sums?

[AnnaZ]

I have two tables - Hours and Employees. Employees table has these fields:
EmpID, and First, and Last name.
Hours table has these fields:
EmpID, Hours, Funding Source, Nature of Work, Pay Period

I need to produce a report or a query that will display these results:
A column with a list of all the employees and all the Nature of Work categories going across with the sum of hours for each Nature of Work. I'm attaching an Excel spreadsheet that shows kind of what I need. Also, these reports need to be produced for each Funding Source and
Pay Period, so something like [Enter Funding Source] and [Enter Pay Period]

My question is that I don't know how to make a report that will just list a column with all the names and count the sum of hours for each Nature of Work.

Hope this is not too confusing, and any help would be greatly appreciated. thank you!

 

[ejstefl]

You'll need to do a totals query - but its pretty easy. Design a new query, adding your hours table and your employee table and joining them on EmpID. Add First and Last name, Hours, Nature of work, Funding Source, and Pay Period.

For the criteria of Funding Source, put [Enter Funding Source?]. For the criteria of Pay Period, put [Enter a Pay Period].

Click the totals button (the Sigma sign - looks like an E). This will add a Totals line to your query. Leave everything on this line at Group By except for Hours. Change hours to total.

When you run this query, it will ask you to enter the funding source and the pay period. After you do that, you'll get a report summed by hours and grouped by employee and nature of work.

 

[AnnaZ]

Thank you! when you say "Change hours to total" you mean change it to "sum"?

I tried this and it almost works. It does sum the hours and groups by employee and the nature of work. However, if one employee has several natures of work, it will list it as a separate record, therefore repeating the name of the employee. What I need is all the employees listed once, with columns for each nature of work and the sum of hours for each nature of work category. Like how it is in the spreadsheet attached to this post.

Is there a way to do this?

thanks!

 

[ejstefl]

Sorry, I did mean sum.

Try a crosstab query. The wizard is pretty good, and should walk you through it.

 

[AnnaZ]

Thank you, I tried the crosstab query and it works beautifully.
I have another question though... I'm grouping my results by Employee Last Name and displaying the sum of Hours for each Nature of Work. However, if a certain employee doesn't have any hours, that Employee doesn't get included in the results. Similarly, if there are no hours for a certain Nature of Work, it doesn't get diplayed either. Is there a way to include ALL employees and list ALL Natures of Work, even if there're 0 hours? Maybe there's a function or something that will include all records in the field, even if the result is 0?

thank you.

 

[ejstefl]

In your query design view, double click on the lines that join your employees table and your hours table. Select the option that says "Include ALL records from 'Employees' and only those records from 'Hours' where the joined fields are equall." This should do it...

 

[AnnaZ]

I tried that and it didn't change the results - I still only get the Employees that have hours and not all the employees... any idea why or how else I can do this? thank you.

 

[ejstefl]

I did a quick test, and it worked just fine for me. Can you post your DB so I can take a look?

 

[AnnaZ]

Here is the database.
I also need all the Nature of Work displayed in the results. so far it's just picking up the ones that have hours. Similarly to Employees.
Thank you for all your help!