Lookup Fields are evil? Or is this ok? Need a little guidance (1 Viewer)

Anthony.DG

Registered User.
Local time
Today, 10:04
Joined
Oct 18, 2019
Messages
27
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.:eek:
 

June7

AWF VIP
Local time
Today, 09:04
Joined
Mar 9, 2014
Messages
5,423
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

I’m here to help
Staff member
Local time
Today, 10:04
Joined
Oct 29, 2018
Messages
21,358
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

Forum Troll
Local time
Today, 12:04
Joined
Oct 6, 2019
Messages
2,884

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 04:04
Joined
Jan 20, 2009
Messages
12,849
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

Immoderate Moderator
Staff member
Local time
Today, 12:04
Joined
Feb 28, 2001
Messages
26,999
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

AWF VIP
Local time
Today, 09:04
Joined
Mar 9, 2014
Messages
5,423
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

Registered User.
Local time
Today, 10:04
Joined
Oct 18, 2019
Messages
27
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

I’m here to help
Staff member
Local time
Today, 10:04
Joined
Oct 29, 2018
Messages
21,358
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

AWF VIP
Local time
Today, 09:04
Joined
Mar 9, 2014
Messages
5,423
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

Super Moderator
Staff member
Local time
Tomorrow, 04:04
Joined
Jan 20, 2009
Messages
12,849
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

Super Moderator
Staff member
Local time
Today, 13:04
Joined
Feb 19, 2002
Messages
42,971
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

AWF VIP
Local time
Today, 09:04
Joined
Mar 9, 2014
Messages
5,423
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

Super Moderator
Staff member
Local time
Today, 13:04
Joined
Feb 19, 2002
Messages
42,971
The point was consistency, not that the underscore was inherently bad.
 

June7

AWF VIP
Local time
Today, 09:04
Joined
Mar 9, 2014
Messages
5,423
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.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 17:04
Joined
Jan 14, 2017
Messages
18,186
Whilst I can see the merits in using _fk / _pk suffixes, I agree with Pat's comments and never use them myself.
 

Mark_

Longboard on the internet
Local time
Today, 10:04
Joined
Sep 12, 2017
Messages
2,111
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

Super Moderator
Staff member
Local time
Today, 13:04
Joined
Feb 19, 2002
Messages
42,971
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.
 

Users who are viewing this thread

Top Bottom