How to do a One to Many

[davidckeith]

How do you link 1 table to another in a 1 to many relationship. This is another way of saying record 17 in Table_X "has a" or "Uses a" record(s) in Table_Y.

[hellojosie]

I hope this is useful.

It is assumed that you have set up the primary and foreign keys in the tables first.

Click on Database Tools Tab
Click on the Relationships button

The show table box will automatically appear. If it doesnt Click on the Show Table button in the Relationships group.

The show table box displays your tables.

Double click on the tables that you wish to create relationships between.

Click on the primary key from the 1 relationship table and drag to the foreign key on the many relationship table.

The edit relationships box will appear.
Click to check enforce referential integrity.

[June7]

Establishing the relationship in Relationship builder can serve to control data integrity. Also, when you build queries with these tables, the join will automatically set according to the established relationship. Building relationships can be helpful but is not critical to database design. In fact, limitations of the Relationship builder can make it impossible to set all table relationships in some larger databases. This is the case with one of my projects that has about 300 tables. Of these, 200 would have to be related to the same master table. Can't be done. I control data integrity with code. That project has only 11 tables with established relationships.

For data entry, use a form/subform arrangement. Main form bound to the 1 side and subform bound to the many side. Master/child link properties of the subform container will synchronize the data.

[Access_Blaster]

Here is a example of a 1 to many relationship.

[davidckeith]

Thanks for the quick reply, hellojosie, and June7. I didn't indicate my needs very well, but it is the query and coding part that I'm most interested in. For now I'll consider this thread closed, but I'm sure I'll have more questions later down the rode. hellojosie your response reminded me of what I still have to do before I start dealing with the coding. Thanks for both of you.