Be patient with this video, it’s longer than normal (over 7 minutes) but it covers the essentials when designing and creating a relational (one-to-many) database using two tables.
Keep the rules simple and I have three which I want to share with you:
- Make sure the field’s data type is the same size (and where applicable the same size)
- Ideally, keep to the same name fields which you are joining – it will help you. Access doesn’t really care!
- When creating your join, do this before you add any data in either table especially when setting the ‘Enforce Referential Integrity’ option. Microsoft Access will actually check the data integrity (if any data exists).
Setting the relationship between tables is carried out in one of two places; the Relationship Window (as in the tutorial) and the other in a query itself but doesn’t offer the ‘Enforce Referential Integrity’ option.
There is more to this – it only the start but it is clear to follow and worth a view.