By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,830 Members | 682 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,830 IT Pros & Developers. It's quick & easy.

Need help forming a query

Expert 100+
P: 1,287
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
Dec 23 '08 #1
Share this Question
Share on Google+
6 Replies


FishVal
Expert 2.5K+
P: 2,653
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.
Dec 23 '08 #2

Expert 100+
P: 1,287
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.
Dec 23 '08 #3

FishVal
Expert 2.5K+
P: 2,653
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.
Dec 23 '08 #4

Expert 100+
P: 1,287
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?
Dec 23 '08 #5

Expert 100+
P: 1,287
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.
Dec 23 '08 #6

FishVal
Expert 2.5K+
P: 2,653
You are welcome.
Good luck.
Dec 23 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.