Lookup Fields are evil? Or is this ok? Need a little guidance

[Anthony.DG]

I've read in a few places that lookup fields should be avoided but I'm not sure why exactly. Should they always be avoided? It's made me feel that I'm doing everything with my database wrong. :banghead:

I'm making a database to keep records for a Trucking company that sells soil and different kinds of rock and want to track a bill of sale. Print out one copy for them and one copy for the sales department. (its a small business)

The main table is what I'm using to make a form off of.

Can anyone tell me if this looks ok? Every relationship has a lookup field on the main table. I just need maybe some steering into the right direction or if it looks like I'm going the right way about this. Thanks in advance.

 

[June7]

Don't build lookups in table, build comboboxes and listboxes on form.
Review http://access.mvps.org/Access/lookupfields.htm

Should not have Carriers in tblMainData, carrier is available by association with truck.
Should not have Customers in tblMainData, customer is available by association with job.

 

[theDBguy]

Hi. Lookup fields have nothing to do with relationships, not really. So, if you have any lookup fields in your table, then take them out. What your image is showing are foreign keys, which are fine to have, as long as they're not lookup fields.

 

[vba_php]

I think if you talk to the majority of the veterans that hang out here, they will tell you that they should be avoided. I've read a few articles from access experts on the issue but it's been some years ago. I think, if I remember right, the main gripe is about data being confused with other data in the database. I'm not sure what you would call that...duplication? confusion?

https://www.google.com/search?q=ms+access+why+lookup+fields+are+bad

http://access.mvps.org/access/lookupfields.htm

 

[isladogs]

Whilst there may or may not be flaws in your table relationships, you appear to be using lookup tables. If so, that's fine.

The problem is with lookup fields in tables. These are shown as combo boxes in table datasheet view. Here are two links to explain why they are a very bad idea
http://access.mvps.org/Access/lookupfields.htm
http://www.mendipdatasystems.co.uk/table-lookup-fields/4594445135

 

[Galaxiom]

Lookup fields at table level makes it impossible to see what data is is actually in the table. The Caption property of the fields in the table are worse. They make it impossible to actually see the name of the field.

I do occasional implement table level lookups in the early phase when I am using the Form wizard. The lookups will automatically construct the appropriate controls on the form. Once that is done I remove the lookups from the tables.

 

[The_Doc_Man]

I'm going to clarify one fine point. June7's advice about "<something> is available by association with <something else>" was indeed accurate. She was explaining something to do with table normalization.

The idea is that you don't store something in two places if they represent the same thing, because in so doing, you introduce confusion as to which one is definitive. More specifically, if you have the same field in two related tables and they ever become different, how will you know later which one is correct.

For this reason, when you can find data in the context suggested by June7's post, exploit the relationship rather than duplicating the data.

 

[June7]

I wouldn't even temporarily create lookups in table. If same combobox needed on multiple forms, use copy/paste. I doubt same combobox is often needed on multiple forms.

 

[Anthony.DG]

Lookup fields at table level makes it impossible to see what data is is actually in the table. The Caption property of the fields in the table are worse. They make it impossible to actually see the name of the field.

I do occasional implement table level lookups in the early phase when I am using the Form wizard. The lookups will automatically construct the appropriate controls on the form. Once that is done I remove the lookups from the tables.

Wait wouldnt "remove the lookups from the tables" mean deleting them? Or is there a way to remove a lookup from a field without deleting said field?

 

[theDBguy]

Wait wouldnt "remove the lookups from the tables" mean deleting them? Or is there a way to remove a lookup from a field without deleting said field?

Hi. Deleting a lookup from the table simply means changing the Display Control from either Combobox or Listbox to Textbox under the Lookup tab.

 

[June7]

Will have to run Compact & Repair. If you don't, could change DisplayControl back to Combo Box and its settings will restore. Even then, if created with Lookup Wizard there is still a relationship that would prevent deleting field.

 

[Galaxiom]

Will have to run Compact & Repair. If you don't, could change DisplayControl back to Combo Box and its settings will restore.

Have not seen that happen but I don't do it often. Usuaully only if I am throwing something together quickly. I always C&R before creating the runtime version.

Even then, if created with Lookup Wizard there is still a relationship that would prevent deleting field.

Absolutely nothing wrong with having the Relationship of a lookup field recorded. It is just not advised to display the lookup in the table.

 

[June7]

That's one point of view. Another is the names should be somewhat different so they are easier to reference without having to include table/query prefix. Such as: CarrierID_PK and CarrierID_FK.

 

[June7]

I agree about consistency in naming convention. I understand you were not indicating underscore was bad. But I did have issue with suggestion field names should be same. Actually first time I've ever heard anyone promote that.

 

[isladogs]

Whilst I can see the merits in using _fk / _pk suffixes, I agree with Pat's comments and never use them myself.

 

[Mark_]

One special note about using lookups in ACCESS; you will often want to base them on a query rather than the underlying table. This means you can create queries that return just the subset that is relevant in an easy to use manner.

In your particular case, you could create a query on truck number that has the Truck number's Primary Key, the displayed truck number, and the parent carrier's carrier name for use when you want to look up the truck number. This also means you can then sort the truck numbers by carrier to logically group them together. Very easy to do when defining a query but a little more difficult if you try to work it into the source for a combo box.