Access Course/Student Database
I work in an educational environment. I have created a database that includes a table of courses, certificates & students. The relationships are correct, because when I look in certificates, I can see the courses for that certificate and under that the students enrolled in that course. Same with the students table, I can see their courses and the certificate they are in. My question is: I need to create a form with drop-down's that will select a course then return a report with students that have not taken that course within that certificate. I have tried queries, macros, etc. I'm not as competent on Access 2007 as I was with the earlier versions. But I've been working on this for some time and can't figure it out. I don't know much about the codes. Could someone help walk me through this? I'm taking courses later this summer, but we need this information now!
Thanks,
Software/Hardware used:
Software/Hardware used:
ASKED:
April 24, 2009 1:23 PM
UPDATED:
May 5, 2009 4:32 PM
RELATED QUESTIONS
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
All Answer Wiki Contributors:
Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.
I guess that a simple query may do the job but we need at least the table structure and relations to help you.
The relationship is that the student is related to the course that is related to the certificate. Example: A Smith has taken 3 courses; Public Speaking, Early Vocal Development, and Speaking the Easy Way; and these courses are under the Certificate – Speaking. So the relationship is the in the table Certificate; Speaking would be a record. When you expand Speaking (the + sign on the left) the 3 courses would drop-down along with anyother course that might be offered under the Speaking Certificate. Then the same with students. Under all three courses, A Smith would be in that course. I need to retrieve, A Smith’s complete address, phone number, email address to send him upcoming course information. We could be offering a new course under Speaking (How to Correct Lisping), and I want A Smith to be included in the new advertising campaign. But maybe J. Loves has already had that course and I don’t want to include him. But J Loves has had Public Speaking also. When I do a simply query, I would still get J Loves because he has also taking Public Speaking. And I will get 3 A. Smith’s because he has taken 3 course prior. I thought a query with a subquery would work, but it doesn’t give me the data I need. I created a query for FullName then a query for Course asking for the course number then a query for FullName and Course to show only Full Names that are not equal in the 2 queries. Well that doesn’t work. I get all the names listed twice. I’ve tried a union query also. Can’t seem to make that work either.
Thanks for your help. I figured it out.