1

I've been having the same issue described here. I'm having trouble recognising whether I have to set up a relationship between two tables that are linked by a lookup field. In my specific case, I have a StudentsT table with a [CountryID] field, and a CountriesT table with [ID] and [CountryName] fields. I already set-up a 1:M relationship between [ID] in CountriesT and [CountryID] in StudentsT (though I don't know if I'm suposed to do that).

Now comes the confusing part, and something that, despite its usefuleness, Gord Thompson's answer to the linked post didn't address, or I didn't understand (probably because it is such a basic thing). I'm not working with forms yet, so this is all at the table level. Do I set up the [CountryID] in StudentsT as a lookup to the [ID] in CountriesT? Or do I leave the relationship as is, and the only apply the lookup in the form itself?

I ask this because if I use the lookup at the table, the 1:M relationship I set up earlier seems extremely redundant (that's why I don't know whether it was ok to make that relationship, since the lookup already limits the input options to those stored in the table, ensuring integrity, right?).

Thanks for any clarification.

Community
  • 1
  • 1
  • 2
    Unfortunately there are multiple ways to do this, as such it's mostly opinion as to which is "right" and which is "wrong".. my opinion, lookup fields in table are evil and should be avoided at all cost. Any type of lookup or combining data via relationships should be left to queries. – Gene Jul 22 '15 at 11:26
  • @Gene Yeah, for some reason it does not feel right. After all, data input is supposed to take place in forms, not in table design, right? – Nicolás Fernández Fioretti Jul 22 '15 at 11:34
  • @NicolásFernándezFioretti Take a read through [this](http://access.mvps.org/access/tencommandments.htm). Specifically number 2, which segues into [this](http://access.mvps.org/access/lookupfields.htm). – Newd Jul 22 '15 at 12:03
  • Gordon tells it all in that link. And Gene is right: " .. lookup fields in table are evil and should be avoided at all cost." If you need to combine data, use a query, that's what they are for. Further, you should never - in user mode - operate at the table or query level. Use forms and reports. – Gustav Jul 22 '15 at 12:05
  • @Gustav Thanks, but I'm still confused about the role relationships play in all this. Are they redundant? When I create a lookup field in a table (despite its sinfulness), is a relationship created? If so, is it a normal relationship that can be viewed in the Relationship Layout View? – Nicolás Fernández Fioretti Jul 22 '15 at 12:14
  • The second post that Newd linked goes more into detail on the evilness of the lookup field. the simplified version is that it actually attaches a query to your table. Every time you use the table, you use the attached (hidden) query and every other table associated with it. – Gene Jul 22 '15 at 12:17
  • The Relationships are not redundant. You still create them and link fields, Just not using lookups. These help Access guess your Query Joins which is helpful for query creation, especially for the form level lookups – Evan Jul 22 '15 at 12:57

0 Answers0