Ms-access – ny practical difference between using the Lookup Wizard to create a FK relationship, or using the Relationships window

foreign keyms accessms-access-2016

I'm very new to Access, so I'd appreciate patience if this is a basic topic.

I'm creating a lot of tables which I need to link. I have two tables, Keyword and KeywordOrthography. KeywordOrthography has a KeywordFK field that connects to the Keyword.ID field.

As far as I know there are two ways to connect KeywordFK to KeywordID:

  1. Go into the relationships window, drag Keyword "ID" to "KeywordFK", create a One-to-Many relationship (enforcing referential integrity.)

  2. Clear any existing relationships between the two tables, go into Table Design view, and on the KeywordFK field, select Lookup Wizard. Select get values from another table, and import the ID field from Keyword. Then when I go to input a new record into KeywordOrthography, I can select from already existing Keywords to input.

Both methods create a 1-to-n relationship with the table, and the second way makes it easier to input a new record manually, though I'm not sure if it causes any complications when using SQL or other methods when adding new records. On the other hand, the first method is much faster when it comes to linking tables.

Question is: Are these methods equivalent as I've pre-supposed? Is there an advantage to doing it one way or the other?

Best Answer

Creating a relationship through the relationship pane does one thing: it creates a relationship (obviously).

The lookup wizard, by contrast, can do multiple things:

  • Create a relationship (identical to one created through the relationship pane)
  • Add a field to the table, set that field to be a combo box based on the relationship storing the foreign key (you can do this manually in table design view as well, but it's not as intuitive)
  • Dangerous: make that field a multi-valued field so you can store multiple keys in a single row

If you use that last option, you will run into trouble when migrating, since other databases generally don't support multi-valued fields. Also, multi-valued fields are generally a mess to deal with further on, and I highly recommend not using them.

The second option creates somewhat of a mess as well, as the data that's displayed (data from the linked record) is not equal to the data that is stored (the foreign key). This can lead to unexpected behavior when trying to query/filter/export the data (since sometimes you operate on the displayed data, and other times on the stored data), but is not really a problem when migrating it (as it just migrates the foreign keys). You might need to reapply the combo box settings after migrating, though.

Related Question