Question about relating tables together

[DNS809904]

Hello,
I have a pretty basic question about creating relationships in Access. When in the relationship window that shows all tables and associated relationships, I am a bit confused as to whether you are to drag the parent field to the child field or vice versa. When trying both, it appears to create the 1 to many relationship in the same direction no matter what way I create the relationship. So I am hoping someone will explain this a bit further to me or at least direct me to a reasource to clear up my, and possibly others, confusion. Sorry for my ignorance on this one!
Thanks a lot,
Dana S.

 

[RoyVidar]

Hi!

The indexes on the primary key/foreign key fields, are what usually determine what relationship you get. The primary key is indexed with no duplicates, and the foreign key field is either not indexed at all, or is indexed with duplicates allowed.

So, it doesn't really matter which direction you drag and drop.

For testing purposes, check what happens if you alter the index of the foreign key field to Yes - no duplicates -> one-to-one (don't forget to alter it back again)

 

[DNS809904]

Thanks for the info!

Hello and thanks for the info.
I was wondering what determined that and now I know so thank you much! I understand the concept of indexes but haven't utilized them to their fullest extent most likely. It seems like Access automatically sets fields of the 'number' data type to index the field automatically.
Also, is it best to always have the foreign key indexed as well or is the primary key sufficient?
Thanks,
Dana

 

[RoyVidar]

Hi again!

Access does something with indexes, but not 100% automatic, let's say semi automatic

When you select one or more field as primary key, a unique index is automaticly created for that/those fields.

For other fields, have a look in Tools | Options - table/query tab. The contents of the "AutoIndex on Import/Create" field, determines wich names/extentions will be indexed "automaticly" when created (hence the "semi automatic").

I would recommend indexing the foreign key fields too, which will speed up join operations on the tables. Also one often hear the recommandation of indexing every field you're going to use in where clauses in sql statements. But do prioritate, I've seen recommandations of max 8-10 indexes on one table. And - the more indexes, it might slow down updates.

 

[DNS809904]

Thanks again!

Roy,
Wow, thanks for the insight with indexes... that really clears things up for me!
Best regards,
Dana S.