Forum home » Delegate support and help forum » Microsoft Access Training and help » Relationships
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Relationships
Resolved · Medium Priority · Version 2003
Reply Tue 3 Aug 2010, 11:35Delegate Mandy said...
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.
For upcoming training course dates see: Pricing & availability
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
Reply Mon 16 Aug 2010, 11:47Delegate Mandy said...
RE: Relationships
I had alreay set up the relationships. I managed to solve this myself aby using fields from all 3 tables in the form.
|