Query Help!!

[StephanieHarris]

Hello,

I am using CR 9 with an access database and I am trying to create a report by writing a command statement to retrieve the data. I've been fighting with this one all day and can't get it.

I have three tables that I'm trying to join together. I've never had any problem when I'm joining two tables but with three I can't get it.

I have a Group, Hour and Detail table.

The Group Table consists of the Group field and the Member field. The Hour table simply has one record for each hour of the day. So there are 24 records. I use it just so I can show every hour of the day in my reports. The Detail table has all of the activity that I need to report on.

My report needs to have three groups: Group, Member, Hour of Day. I then need to show a count of activity by Hour.

Group 1

Member 1

00:00 : 100

01:00 : 75

....

23:00 : 20

Member 2

etc...

The following SQL statement works but it only returns an hour group for hours that actually have data on the Detail table:

Select Group.GroupNumber, Group.MemberNumber, Hour(Detail.StartingTime), Count(Detail.ReferenceNumber)

From Group left join Detail On Group.MemberNumber = Detail.MemberNumber

Group By Group.GroupNumber, Group.MemberNumber, hour(Detail.StartingTime)

Is there a way I can incoroporate another join into that statement so I can also bring back all of the Hours from the Hours table?

If I could incorporate this into my sql as well I think I would be in good shape. I'm just not sure how do do it...

Select Hour.Hour 

From Hour Left Join Detail On Hour.Hour = Detail.StartingTime

Hopefully this makes sense. I sincerely appreciate your help with this!

Stephanie

[malleyo]

Try this:

SELECT Group.GroupNumber, Group.MemberNumber, HOUR(Detail.StartingTime), COUNT(Detail.ReferenceNumber)
FROM Group 
LEFT JOIN Detail On Group.MemberNumber = Detail.MemberNumber
LEFT JOIN On Hour.Hour = HOUR(Detail.StartingTime)
GROUP BY Group.GroupNumber, Group.MemberNumber, HOUR(Detail.StartingTime)