Discussion Locked

Many to May Relationships in Access 2000

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have a database with a many-many relationship. I have built the relavant 'junction box' or 'join table' to break down the lin to two 1 to many relationships. Fine! My problem is how to populate the tables. The scenario is to add a childs details i.e. name adress etc.. (childid = primary key) For each child there can be many carers so I need to add a carer which is no problem (carerid = primary key), the problem is I need to automatically populate the join table which contains (childid and carerid) but cant seem to get it to work. This is really important and I an desperate to find a solution, can you help!!

3 replies

by donq

Your join table has two (foreign key) fields that combine to make up each unique record.

I create a table maintenance form with two combo boxes and attach a select query to each Row Source property with a Primary Key query from one of my (1) tables. Make the user select from existing records only and set the two (foreign key) fields as the look-up tables Primary Key (or a 2 field complex key).

Works great, and easy to build!

Build your Primary Key queries in query view, switch to SQL View when complete and copy ([Ctrl]+C). Then open the combo box property window and focus on Row Source and do a paste ([Ctrl]+V).

This discussion is closed to new replies.