SQL Syntax on Distinct records
Posted on 2005-05-04
My site's basic database structure is as follows:
facultyinfo table contains faculty information
training table contains inservice training opportunities
participants table contains the FacultyID from the facultyinfo table and the TrainingID from the training table along with a completion status (No, CEU, Graduate, Undergraduate)
I have queries that total the number of hours completed if the completion status is (CEU, Graduate, Undergraduate). Since the query uses an SQL Select Disting Row statement it will show two records it is possible to show two, or more, records if some courses were taken for CEUs, some for Graduate credit and some for Undergraduate credit. Is there a better way to create this query so I can get the total number of hours completed but aggregate each faculty as only one record. My current report shows a faculty member two, or more, times if they have more than one completion status (CEU, Graduate, Undergraduate). I understand this is because the record is not seen as distinct since it contains a different completion status.
I'm not sure whether to address this on the database design side or if I can modify the SQL query.