Queries & Forms

[aharmon]

Hi. I hope you can help me. I have an access table which keeps up with students and what classes they have attended and another on that has all the classes offered.

Here is an example:

tblStudents
Student_ID FirstName LastName ClassesToSearch
1 John Smith 1,4,6
5 Wendy Whalen 6,9
3 Mary Green 4,5

tblClasses
Class_ID Name
1 New Employee Orientation
2 Who Moved My Cheese
3 True Colors
4 Claims Data Entry
5 Claims Adjudication
6 Fraud
7 Lunch -n- Learn
8 Angelas Testing
9 Donnie's Class
10 Krista's Class
11 Here is one more class.


The Training Coordinator enters which classes a student has attended. For this particular one the Training Coordinator has highlighted Fraud(6) and Donnie's Class(9) because Student_ID 5 has attended both of these classes. Now, when you click on Run Report I want to bring up a report that shows the students information along with all the classes that student has attended.

So what I have so far is a report where the record source is a query that I have written. The query looks like this in the SQL View:

SELECT tblStudents.Student_ID,
tblStudents.FirstName,
tblStudents.LastName,
tblClasses.Name
FROM tblStudents,
tblClasses
WHERE (((tblStudents.Student_ID)=[Forms]![frmStudents]![Student_ID])
AND ((tblClasses.Class_ID) In ([Forms]![frmStudents]![txtClassesToSearch])))

I cannot get this to work. If there is only one class selected this query will work. Also, if I enter a few numbers where the query looks like the one below, it will work:

SELECT tblStudents.Student_ID,
tblStudents.FirstName,
tblStudents.LastName,
tblClasses.Name
FROM tblStudents,
tblClasses
WHERE (((tblStudents.Student_ID)=[Forms]! [frmStudents]![Student_ID])
AND ((tblClasses.Class_ID) In (6,9)))

Can you please help me? If you have any questions, please let me know. Thank you in advance!

 

[THoey]

I would suggest that you break the ClassesToSearch field off from your Students table and make another table called ClassesTaken:

StudentID Foreign Key from Student table
ClassID Foreign Key from Classes table

In this table, each student would have one record for each class they had taken. This will make your query a bit easier to write. Terry M. Hoey