Access flag record when match found in table 2 (one-to-many relationship)

Question

I have an educational db with a tbl_Students and tblStudentPrograms. The tblStudentPrograms has one record per student and program (ProgramID) per year (YearID.

I need to find out how many students participated in ProgramID=2 EVER. So, I need the DISTINCT subset of students who have participated in the program for any YearID.

(Of course, this will be complicated further by trying to find other records in other tables such as StudentAdvising as well , but this will be a good start.)

Thank you!

Please post the code you are having trouble with. – Andrew Jul 23 '14 at 16:57 — Andrew, Jul 23 '14 at 16:57

score 0 · Answer 1 · answered Jul 23 '14 at 16:57

0

SELECT DISTINCT count(studentID) FROM tblStudentPrograms WHERE ProgramID = 2

answered Jul 23 '14 at 16:57

serakfalcon

3,501
1
22
33

Seems as though something is wrong. It gives me a count greater than the number of students in my tblStudents – user3869722 Jul 23 '14 at 21:16

score 0 · Answer 2 · answered Jul 23 '14 at 17:00

0

Assuming you want a distinct count of students (excluding those where the same student may have taken a program twice...

SELECT count(distinct StudentID) 
FROM tblStudentPRograms
WHERE ProgramID = 2

answered Jul 23 '14 at 17:00

xQbert

34,733
2
41
62

score 0 · Answer 3 · edited May 23 '17 at 11:57

0

Assuming you want a distinct count of students (excluding those where the same student may have taken a program twice...

SELECT count(distinct StudentID) 
FROM tblStudentPRograms
WHERE ProgramID = 2

though I'm not positive access supports a distinct w/in a count like other RDBMS do...

so you may have to do:

SQL : how can i count distinct record in MS ACCESS

SELECT count(BStudentID) as DistinctStudentsInProgram
FROM (select distinct studentID, ProgramID from tblStudentPrograms) B
WHERE B.ProgramID = 2

edited May 23 '17 at 11:57

Community

1
1

answered Jul 23 '14 at 17:02

xQbert

34,733
2
41
62

Used the code from your link and that is actually what I wanted -to show the records with the count. Thank you! – user3869722 Jul 23 '14 at 21:52
Now can I piggy back on this to further refine this set of students to show the number which also have a record in tblStudentAwards? – user3869722 Jul 23 '14 at 21:53

Access flag record when match found in table 2 (one-to-many relationship)

3 Answers3