Calculations and sorting within a query 2

[SMHSleepy]

Here's a simplified version of my database:
I have 2 tables:
tblPersons (personID, LName, FName)
tblWorkLoad (personID, WorkMonth, WorkType, WorkHours)

I'd like to make a query which calculates the hours worked for each type of work for each person.
For example, if the tables were populated as follows:
tblPersons.personID tblPersons.LName tblPersons.FName
1................................Smith......................John
2................................Doe........................Jane

tblWorkLoad.personID tblWorkLoad.WorkMonth tblWorkLoad.WorkType tblWorkLoad.WorkHours
1...................................August................................administrative........................8
1...................................August................................administrative........................8
1...................................August................................technical................................8
1...................................August................................technical................................8
1...................................August................................technical................................8
2...................................August................................administrative........................8
2...................................August................................technical................................8
2...................................August................................technical................................8
2...................................August................................technical................................8
2...................................August................................technical................................8

the query results would produce:

tblPersons.personID tblPersons.LName tblPersons.FName tblWorkLoad.WorkMonth tblWorkLoad.WorkType tblWorkLoad.WorkHours
1...................................Smith........................John.......................August...................administrative...................16
1...................................Smith........................John.......................August...................technical...........................24
2...................................Doe..........................Jane........................August...................administrative...................8
2...................................Doe..........................Jane........................August...................technical...........................32

I know I am able to do this with a bunch of separate queries first isolating each person, then the work type, then using a form to select the month and displaying the desired results but I'm sure there is an easier way, probably with a simple sql code. Can anyone offer such a simple solution? Thanks.

 

[dhookom]

First, you can use TGML with the TT tag to display your post in a fixed space font so you don't have to include all the "....." to align the columns in your post. We do appreciate the effort ;-)

This looks like a standard totals query of tblPersons and tblWorkLoad. Click the Totals icon which opens the "Total:" line in the bottom pane. Then add the fields you want to display and set the WorkHours to "Sum" rather than "Group By".

BTW: I think it is a mistake to store month with the month name. The month number (8 rather than August) provides much greater flexibility/functionality.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Something like this ?

SELECT P.personID,P.LName,P.FName,W.WorkMonth,W.WorkType,Sum(W.WorkHours) AS SumOfHours
FROM tblPersons P INNER JOIN tblWorkLoad W ON P.personID=W.personID
GROUP BY P.personID,P.LName,P.FName,W.WorkMonth,W.WorkType

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SMHSleepy]

Thanks Duane and PH but these solutions only give me total hours. I need it to give me totals for each type of work (i.e. administrative vs. technical). Maybe I'm not seeing it properly?

 

[PHV]

What is YOUR SQL code ?

 

[SMHSleepy]

Well, like I said, my example was a simplified version. Here is my code for what it's worth.

SELECT tblPersons.autoIDPerson, tblPersons.SortOrder, tblPersons.FirstName, tblPersons.LastName, tblPersons.Initials, tblPersons.NotWorking, tblWorkloadhrs.CounterID, tblWorkloadhrs.WorkLoadDate, tblWorkloadhrs.Tech, tblWorkloadhrs.WorkLoadID, tblWorkloadhrs.Category, tblWorkloadhrs.Description, tblWorkloadhrs.Hours, tblWorkloadhrs.Explanation, Sum(tblWorkloadhrs.[hours]) AS Expr1
FROM tblPersons RIGHT JOIN tblWorkloadhrs ON tblPersons.[autoIDPerson] = tblWorkloadhrs.[Tech]
GROUP BY tblPersons.autoIDPerson, tblPersons.SortOrder, tblPersons.FirstName, tblPersons.LastName, tblPersons.Initials, tblPersons.NotWorking, tblWorkloadhrs.CounterID, tblWorkloadhrs.WorkLoadDate, tblWorkloadhrs.Tech, tblWorkloadhrs.WorkLoadID, tblWorkloadhrs.Category, tblWorkloadhrs.Description, tblWorkloadhrs.Hours, tblWorkloadhrs.Explanation
HAVING (((tblWorkloadhrs.WorkLoadDate)>=[forms]![WorkLoadHrs]![Start] And (tblWorkloadhrs.WorkLoadDate)<=[forms]![WorkLoadHrs]![End]));

 

[SMHSleepy]

This doesn't even give me a total actually. The Expr1 field just displays the same thing as tblWorkloadhrs.hours

 

[SMHSleepy]

When I try to just GROUP BY tblWorkloadhrs.hours, I get the following error message:
You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)

 

[PHV]

The Expr1 field just displays the same thing as tblWorkloadhrs.hours
So, remove the tblWorkloadhrs.hours from the select list and from the group by list.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SMHSleepy]

I still don't get it. I'm either getting the same thing or an error no matter what I try.

 

[SMHSleepy]

Okay, I started over instead of trying to modify my mess and now it works! I must have been including extraneous fields that caused problems. Thanks for your help!

 

[SMHSleepy]

SELECT tblWorkloadhrs.Tech, Sum(tblWorkloadhrs.Hours) AS SumOfHours, tblPersons.FirstName, tblPersons.LastName, tblWorkloadhrs.WorkLoadID
FROM tblPersons INNER JOIN tblWorkloadhrs ON tblPersons.[autoIDPerson] = tblWorkloadhrs.[Tech]
GROUP BY tblWorkloadhrs.Tech, tblPersons.FirstName, tblPersons.LastName, tblWorkloadhrs.WorkLoadID;