I'm pretty sure I'm having a off day, but I can't wrap my head around this query.

I need to get sums of hour for each month for each clients. Here's my SQL so far.

SELECT
[LastName] & ", " & [FirstName] AS [Client Name],
qtrMonthSorted.Month, 'Names every month in fiscal year order
Sum(tblDuration.Duration) AS [Total Hours]
FROM
qtrMonthSorted,
tblPerson INNER JOIN (tblDuration
INNER JOIN (tblClients
INNER JOIN tblVisit
ON tblClients.PersonID = tblVisit.PersonID)
ON tblDuration.DurationID = tblVisit.DurationID)
ON tblPerson.PersonID = tblClients.PersonID
GROUP BY
[LastName] & ", " & [FirstName],
qtrMonthSorted.Month, qtrMonthSorted.MonthID
ORDER BY
[LastName] & ", " & [FirstName], qtrMonthSorted.MonthID;


My brain says I should be able to add another query to retrieve the Sum() with a WHERE clause to retrieve the current Month # from the qtrMonthSorted, but my guts disagree.

So ... I not sure I am following you ... you think you can do the SUM() in qryMonthSorted? ... {What is the SQL of qryMonthSorted?} ... or you think you can add a WHERE clause in the currently posted SQL to help par down the information? ... or ... you just want to push the Sum() expression down to a lower level?

qtrMonthSorted is just two column, with Month Name arranged in order of our fiscal year.

SELECT MonthID, MonthName FROM tblMonth ORDER BY MonthID;

The output would be something like:

PersonA Jul XofHourHourForJuly
PersonA Dec XofTotalHourForDec
PersonB Aug XofTotalHourForAug
PersonB Dec XofTotalHourForDec


I ended using UNION, each individually retrieving a sum of hours for a given month for a given client, then used that as a subquery to provide Group by Person, Month, Hours, but I'm quite sure there is a better solution than UNION'ing 12 times with different BETWEEN clauses per query...

Which other table has MonthID in it? ... any of them? ... I assume that you have a Date of some sort in tblVisits and/or tblDuration? ... Or does tblMonth have other fields in it, like a startdate or something?

... Only asking so I can get as much of info as possible so we can create an efficient SQL ... because I agree that 12 UNIONs is not the best way to go!! ... :o