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.

 

[Pat Hartman]

One more comment on the schema. Using different names for PK-FK pairs is confusing to people unfamiliar with the schema and using multiple differences - CarrierID, Carriers, Carrier is even worse. Use CarrierID in all tables so it is clear, even without viewing the relationship window, which fields the join should be on. Also avoid special characters and embedded spaces like the plague. Be aware that simple, non-compound words are likely to be reserved. Names like "Date" and "Name" are particularly problematic since the first is also the name of a function and the latter is the name of a property. What exactly does Me.Name refer to? Is it the "Name" property of the form or is it the control named "Name"? Be consistent in how you create primary key names. Best practice takes something from the table name and if the PK is an autonumber, the suffix is normally "ID". So CarierID, TruckID, OerationID are all good. ID, Material, and Job_ID are "bad". Job_ID is only "bad" because it is inconsistent with all the other PK names. Naming a PK "ID" is just confusing.

 

[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.

 

[Pat Hartman]

The point was consistency, not that the underscore was inherently bad.

 

[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.

 

[Pat Hartman]

If you want to suffix with _pk and _fk, I don't object. I don't do it, but it is not wrong. CustomerID_PK and CustomerID_FK is not confusing. It is the subtle differences like customer vs customerID or cust or custID which I object to. That type of inconsistency just causes confusion. Keep in mind that in queries that join related tables, you don't select both the pk and the fk. You only select the fk so you should never end up with with two fields named CustomerID having to be selected in the same query. Selecting both or just selecting the PK are mistakes made by people who don't understand relationships.