I'm on Access 2010.
I'm designing a database to help keep track of participants in a community program.
I have a table called ParticipantMaster that includes fields like ParticipantID, FirstName, LastName, Address, Phone, etc and an entry form built on this table.
I have a set of forms/tables that collect attendance data (with levels to show whether they attended PARTIAL, HALF, or FULL session)... the fields relevant to my question here include ParticipantID (from Participant Master), WorkshopID, Level.
From the attendance data I've created a query called Attendance_FullorHalf that counts how many Half or Full sessions combined each participant has attended in a field called CountStatus. Fields in this query include include ParticipantID, CountStatus, FirstName, LastName. This query is working correctly.
Separate from attendance, I have a table and form called Interviews that logs every time I call a Participant. They only become eligible for interviews after their attendance Count > 2.
I want the attendance count to display on the interview logging form so I can see right there whether they are interview-eligible.
Maybe a text box that displays something like
SELECT CountStatus from Attendance_FullorHalf
WHERE ParticipantID (from Attendance_FullorHalf query) = ParticipantID (of the record I'm on in the interview form)
I don't know if it's a statement like that that I need -- I just wrote that to help explain my question. If it is a statement like that where do I actually create that??
So if I'm in the interview form, entering info on participant whose ID is 20-563 I want it to display on that form how many sessions participant number 20-563 has attended.
Hope my question is clear -- thank you so much!
I'm designing a database to help keep track of participants in a community program.
I have a table called ParticipantMaster that includes fields like ParticipantID, FirstName, LastName, Address, Phone, etc and an entry form built on this table.
I have a set of forms/tables that collect attendance data (with levels to show whether they attended PARTIAL, HALF, or FULL session)... the fields relevant to my question here include ParticipantID (from Participant Master), WorkshopID, Level.
From the attendance data I've created a query called Attendance_FullorHalf that counts how many Half or Full sessions combined each participant has attended in a field called CountStatus. Fields in this query include include ParticipantID, CountStatus, FirstName, LastName. This query is working correctly.
Separate from attendance, I have a table and form called Interviews that logs every time I call a Participant. They only become eligible for interviews after their attendance Count > 2.
I want the attendance count to display on the interview logging form so I can see right there whether they are interview-eligible.
Maybe a text box that displays something like
SELECT CountStatus from Attendance_FullorHalf
WHERE ParticipantID (from Attendance_FullorHalf query) = ParticipantID (of the record I'm on in the interview form)
I don't know if it's a statement like that that I need -- I just wrote that to help explain my question. If it is a statement like that where do I actually create that??
So if I'm in the interview form, entering info on participant whose ID is 20-563 I want it to display on that form how many sessions participant number 20-563 has attended.
Hope my question is clear -- thank you so much!
