Mandy has attended:
Access Intermediate course
Relationships
I am designing a student database to hold records of all our students and the courses they have attended. I have a Student Table, with name, address, etc and a Course Table with course names and dates and a link table to join the two - pretty much as the Video Rental database we discussed in training. I am now trying to design a Student Details form which has a Course sub form and shows one student record and list all the courses they have attended. But currently the course sub form lists courses attended by ALL students - how can I get the subform to show only the course records that relate to the student whose record is shown in the main form?
Sorry if this is confusing - it's difficult to put into words! I am sure its straightforward but have tried everything I can think of.
RE: Relationships
Hi Mandy, thanks for your query. This sounds like you either need to alter the join type between the two linked tables or run a query to filter out the records you want.
In the relationships panel (Tools - Relationships) double click on the connector between your linked tables and alter the options in "Join Type". In that dialog you can set what recordset you want when you run queries using these tables, in particular whether you want all records regardless of whether there are corresponding records in each table, or just those which appear in one of the tables. If your tables are set up correctly I'm almost certain this will have the effect you are after.
Failing that, create a query that includes the "Is Null" criteria to strip out fields which do not have values in them. This may have the desired effect, but I would try the above option first.
Hope this helps,
Anthony
