Plesae help! I am building a relational database with 2 main tables that should relate in a one to many relationship. I have gone to the design tab created relationship, done the join but cannot enforce referential integrity.
The database tracks managers and their accounts. ONe table tracks managers and the subtable (the many) tracks their accounts.
Of course there are managers without accounts as yet, but Access will not let me enforce ref integrity saying that managers may have records not found in accounts. Of course that's true.
Can you please help me set this straight.
And ultimately, I want to ensure that if I have to delete a manager, I can delete all associated accounts at the same time.
I think Foreign Key may be the answer, but I have no idea how to do so.
Create a table called tbl_managers with the following fields
manager_ref, manager_name etc. Make manager_ref the primary key.
Create another table called tbl_accounts with the following fields account_ref, manager_ref etc make account_ref the primary key. Make sure the manager_ref in both tables have the same data types e.g. both are text or both are bnumber etc.
Using the relationship screen add the two tables to the screen now create a join from the field manager_ref in tbl_managers to the field manager_ref in tbl_accounts. When you finish the join a popup will be displayed then you can finish the join criteria there should be 1 next to the tbl_manager and the infinity next to the tbl_accounts this means that one manager can have many accounts.
Hope this useful, for a better example look at Northwind.mdb sample database
Do you have a Room table that shows the location and the facilites in the room etc.?
If you do have a room table then you will need to change Room to Room_ref so that it can be linkied to the room table
The table structure is the same as my previous email.
A foreign key is a field in a relational table that matches the primary key column of another table. The foreign key can be used to cross-reference tables.
In the Contacts table I added a field called residents_ref. The field residents_ref is the Primary Key (PK) in the residents table and foreign key (FK) in the contacts table When you are adding the contaccts for the residents in the Contacts table you must also included the relevant resident_ref in the contacts table.
There is also a relationship diagram and a report that displays some data sorted by residents.