Querying tables to get totals for one record?? 1

[Beeps]

Hello all,

I have a systems table which is linked by a system ID Number to four other tables for various activities. A system may or may not have entries in all four activities tables.

My question is how do I query the information to get various totals and calculations from these tables and have it listed under the system name(or ID Number)?

currently, I have all the queries together to get the totals for each activity, but I can't get them all in a common query so that I can put the info into a report.

Any help is greatly appriciated. Thanks!!

 

[rac2]

SELECT system.sysID, COUNT(activityOne.sysID), COUNT(activityTwo.sysID), AVG(activityThree.sysID), COUNT(activityFour.sysID)
FROM system a
LEFT JOIN activityOne b ON a.sysID = b.sysID
LEFT JOIN activityTwo c ON a.sysID = c.sysID
LEFT JOIN activityThree d ON a.sysID = d.sysID
LEFT JOIN activityFour e ON a.sysID = e.sysID
GROUP BY system.sysID

Or replace COUNT(sysID) with the various calculations on data in an activity table.

Seems like this might work.

 

[Beeps]

Thanks so much for your rac2!! That was very quick and effective, duhh, I should have thought of that. Works really well.

Have a Happy Thanksgiving!