Join Problem

[Wrinkles]

I have two tables: Hours & Incident

Hours
HoursDate FacilityCode Hours

Incident
IncidentDate FacilityCode LTI (Yes/No) field

How can I join these two tables on the facilitycode so that they are group together by Month on the Hours Table. In other words I want to display all Facility Hours, FacilityCode and any LTIs in that month or for a group of months when selected by the user. I can get the join to work ok for individual months but not for a group of months.

I tried Left Join on the Hours table but had trouble counting the LTI (Yes/No) field.

 

[ecniv]

Probably need a sub/second query to get the counts of LTI with the facility code.

Then get the hours table and left join to the query above.

The following is rough and you should use the query builder to assist you.

Select 
 [hours].facilitycode,
 [hours].hoursdate,
 [hours].[hours],
 [qrySubCountLTI].lti,
 [qrySubCountLTI].NoOfIns

From
 [hours] left join
(
   Select Incident.facilitycode, incident.LTI, count([incident].lti) as NoOfIns
   From Incident
) as qrySubCountLTI on [hours].facilitycode=qrySubCountLTI.facilitycode

This may not work - but might give you an idea?


Vince

 

[Wrinkles]

Thanks Vince,

Got the following SQL which seems close to what I want except that only shows LTIs equal to the date rather than all LTIs in the same month and year as the Hours Date. In other words I want them to be joined and grouped by the same month and year by HoursDate and IncidentDate.

SELECT Hours.HoursDate, Hours.FacilityCode, Sum(Hours.Hours) AS SumOfHours, Query6LTIs.IncidentDate, Query6LTIs.FacilityCode, Count(Query6LTIs.CountOfLTI) AS CountOfCountOfLTI
FROM Hours LEFT JOIN Query6LTIs ON (Hours.HoursDate = Query6LTIs.IncidentDate) AND (Hours.FacilityCode = Query6LTIs.FacilityCode)
GROUP BY Hours.HoursDate, Hours.FacilityCode, Query6LTIs.IncidentDate, Query6LTIs.FacilityCode
HAVING (((Hours.HoursDate) Between [StartDate:] And [EndDate:]));

The crux of this is the Hours.HoursDate = Query6LTIs.Incident and I could use Format("mm,yyyy") so that they join on the same month and year but I'm having syntax problems in doing so.

 

[ecniv]

Let me get this straight; you want the facility codes to be matched, the month and year from the hours and the month and year from the incidents, with the incidents totalled by the LTI field.

Select 
 [qryHours].facilitycode,
 [qryHours].hoursMonth,
 [qryHours].hoursYear,
 [qryHours].totalhours,
 [qrySubCountLTI].lti,
 [qrySubCountLTI].NoOfIns

From
 select
   [Hours].facilitycode,
   format([Hours].hoursdate,"mmmm") as hoursMonth,
   format([Hours].hoursdate,"yyyy") as hoursYear,
   sum([Hours].[hours]) as TotalHours
 from
   [hours]
 group by
   [Hours].facilitycode,
   format([Hours].hoursdate,"mmmm"),
   format([Hours].hoursdate,"yyyy")
) as qryHours

left join

(
 Select 
   Incident.facilitycode, 
   format(incident.incidentdate,"mmmm") as LTIMonth, 
   format(incident.incidentdate,"yyyy") as LTIYear, 
   incident.LTI, count([incident].lti) as NoOfIns
 From Incident
 Group By 
   Incident.facilitycode, 
   format(incident.incidentdate,"mmmm"), 
   format(incident.incidentdate,"yyyy"), 
   incident.LTI
) as qrySubCountLTI 
on 
  qryhours.facilitycode=qrySubCountLTI.facilitycode and
  qryhours.hoursmonth=qrySubCountLTI.LTIMonth and
  qryhours.hoursyear=qrySubCountLTI.LTIYear

This may not work at all (not tested) but may give you an idea?


Vince

 

[Wrinkles]

Vince,

thanks your code had helped and now I can access each individual month Hours and Total LTIs for each facility.

My next trick to show a combined TotalHours and LTIs for each facility for a range of dates entered by the user eg

(1/07/2004 - 31/10/2004)
Facilitycode TOTALHOURS FOR PERIOD TOTAL LTIS FOR PERIOD

ie one total for each facility.

Any ideas would be appreciated - thanks again for your help.

 

[Wrinkles]

Problem now resolved - managed to create two separate queries Grouped by FacilityCode and used the Left Join on the facilitycode - works well.

Thanks for your assistance again Vince