Need help forming a query

[ChipR]

I need a query to see who worked what hours on activities, and I have combo boxes to narrow the results to a specific activity or employee. I can't figure out how to get the sums of different types of hours into the same row.
I had a query that went through every activity and did DSUMs for each category, but it was so slow and messy I had to trash it.

I have a table EmployeeHours with fields
PayPeriod - number
EmployeeNumber - text
ProjectNumber - text
ActivityNumber - text
ActivityTitle - text
HoursType - text
Hours - number

I need a query for a souce for a continuous form with:
project number, activity number, activity title, employee number, sumOfHours where HoursType is RG(regular), sumOfHours where HoursType is OS(overtime), sumOfHours where HoursType is (Anything Else).

Is that possible in one query?

Thanks for any help,
Chip

[FishVal]

Hello, Chipr.

I guess you want the query to be updateable.
If so, then you need:

• 1. Updateable query returning EmployeeNumber/ProjectNumber/ActivityNumber combinations to calculate totals on
• 2. Add calculate fields to the query returning required totals.
• 3. For these calculated fields you could conviniently use VBA function(s) written in such a way that it gets EmployeeNumber/ProjectNumber/ActivityNumber as arguments and returns correspondent total.

[ChipR]

Hi FishVal, thanks for your help.
Is there a faster way to do it if I don't need to update the results? I only need it for viewing.

[FishVal]

Sure.

Two aggregate queries grouping results by EmployeeNumber/ProjectNumber/ActivityNumber combinations - one to calculate overtime total, another to calculate the rest.
Then simply join them on equal values of EmployeeNumber/ProjectNumber/ActivityNumber fields.

[ChipR]

I tried that but it resulted in only the rows in the results of one query or the other, depending on the direction of the JOIN. Is there a way I can get all of them?

[ChipR]

I made a query with the cartesian product of all employees and all activities, joined it with the aggregate queries, and added a Total field: 0+nz(OS,0)+nz(RG,0)+nz(DT,0) where >0 to eliminate the activity+employee rows where there were no hours at all.
Now it's instantaneous compared to the several seconds it took before! Thanks for the help.

[FishVal]

You are welcome.
Good luck.