Interesting many to many query with criterias and left joins

[Alcar]

hi all
I am going through some big time headacke trying to figure out how to get as a result what I need.

here is the situation:

tblUsers
--------
lUser_ID
szFirstName
szLastName

tblLog
------
lLog_ID
lUser_ID
lCompany_ID 'links to the tblAllTasks
lTask_ID 'links to the tblAllTasks
lSubTask_ID 'links to the tblAllTasks
dtStart
dtFinish

tblAllTasks 'Many to Many table
-----------
lTask_ID
szTaskName

This table contains company name, task name, subtask name...

I have a report that prints out all the tasks during the week that a user has done:

Mike Mouse from 09/24/2001 to 10/05/2001

Company Task Subtask Time
------------------------------------------------
ABC Web Site Programming 34

the only problem is that if a employee did the same task and same subtask (in tow different moments) I would like to group them up so I can print only one element for that record.

here is the SQL Query of the View that the report uses:

SELECT dbo.tblUsers.szLastName + ' ' + dbo.tblUsers.szFirstName AS Employee, tblAllTasks_3.szTaskName AS Company,
tblAllTasks_1.szTaskName AS Task, tblAllTasks_2.szTaskName AS [Sub Task], DATEDIFF(mi, dbo.tblLog.dtStart, dbo.tblLog.dtFinish) AS Time,
dbo.tblLog.lUser_ID, dbo.tblLog.dtStart
FROM dbo.tblLog LEFT OUTER JOIN
dbo.tblAllTasks tblAllTasks_1 ON dbo.tblLog.lTask_ID = tblAllTasks_1.lTask_ID LEFT OUTER JOIN
dbo.tblAllTasks tblAllTasks_3 ON dbo.tblLog.lCompany_ID = tblAllTasks_3.lTask_ID LEFT OUTER JOIN
dbo.tblUsers ON dbo.tblLog.lUser_ID = dbo.tblUsers.lUser_ID LEFT OUTER JOIN
dbo.tblAllTasks tblAllTasks_2 ON dbo.tblLog.lSubTask_ID = tblAllTasks_2.lTask_ID


and the filter called to open the report is:

mySQL = "([lUser_ID] = " & Me.Combo8 & &quot " & _
"AND (dtStart between '" & startDate & "'" & _
" AND '" & endDate & "')"

DoCmd.OpenReport "TimeSheet", acViewPreview, , mySQL

seems like I cannot sum the times grouping by subtasks.. I really have no idea of what else I could try.
TIA
Alcar

 

[igk]

Hi,

Can you create group on the report itself (group by Subtask, set group header to yes, and if you need total -- group footer to yes)