Hi, I've done this for you quickly - when selecting the data source for your report instead of adding tables, click the "Add Command" button.
Paste the following code into the dialog box, you will also need to create a startdate and an enddate parameter (datatype date) for the date lookups.
SELECT
f.name,
f.faddr,
f.fstate,
f.fzip,
f.fphone,
f.faxphone,
ft.descr,
f.code,
isnull(x.count,0)
FROM Facilities f
JOIN Facility_Types ft
on f.ftype = ft.code
LEFT JOIN (
SELECT
f.code,
count(t.runnumber) [count]
from facilities f
JOIN trips t
ON f.code = t.ofac
AND t.tdate >= {?startdate}
AND t.tdate <= {?enddate}
GROUP BY
f.code) x
on f.code = x.code
This will return an entire list of all facilities in your database and then perform the count of how many trips were made to each (showing 0 for facilities where no trips were made.
Let me know how you get on.
Regards,
Ryan.
Edited by rkrowland - 04 Apr 2012 at 3:26am