Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-13-2019, 12:48 PM   #1
Anthony.DG
Newly Registered User
 
Join Date: Oct 2019
Posts: 8
Thanks: 8
Thanked 0 Times in 0 Posts
Anthony.DG is on a distinguished road
Lookup Fields are evil? Or is this ok? Need a little guidance

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.

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.

Anthony.DG is offline   Reply With Quote
Old 11-13-2019, 12:54 PM   #2
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,478
Thanks: 0
Thanked 577 Times in 573 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Lookup Fields are evil? Or is this ok? Need a little guidance

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
Anthony.DG (11-13-2019)
Old 11-13-2019, 12:55 PM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,461 Times in 1,442 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Lookup Fields are evil? Or is this ok? Need a little guidance

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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-13-2019, 12:55 PM   #4
vba_php
Software Engineer
 
vba_php's Avatar
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 706
Thanks: 85
Thanked 81 Times in 81 Posts
vba_php is on a distinguished road
Re: Lookup Fields are evil? Or is this ok? Need a little guidance

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+a...fields+are+bad

http://access.mvps.org/access/lookupfields.htm
vba_php is offline   Reply With Quote
Old 11-13-2019, 12:57 PM   #5
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,381
Thanks: 115
Thanked 3,112 Times in 2,830 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Lookup Fields are evil? Or is this ok? Need a little guidance

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/t...lds/4594445135
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 11-13-2019, 01:03 PM   #6
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,719
Thanks: 105
Thanked 1,513 Times in 1,426 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Lookup Fields are evil? Or is this ok? Need a little guidance

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.
Galaxiom is offline   Reply With Quote
The Following User Says Thank You to Galaxiom For This Useful Post:
Anthony.DG (11-13-2019)
Old 11-13-2019, 01:41 PM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,774
Thanks: 93
Thanked 1,729 Times in 1,600 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Lookup Fields are evil? Or is this ok? Need a little guidance

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Anthony.DG (11-13-2019)
Old 11-13-2019, 01:41 PM   #8
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,478
Thanks: 0
Thanked 577 Times in 573 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Lookup Fields are evil? Or is this ok? Need a little guidance

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 11-13-2019, 02:04 PM   #9
Anthony.DG
Newly Registered User
 
Join Date: Oct 2019
Posts: 8
Thanks: 8
Thanked 0 Times in 0 Posts
Anthony.DG is on a distinguished road
Re: Lookup Fields are evil? Or is this ok? Need a little guidance

Quote:
Originally Posted by Galaxiom View Post
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?
Anthony.DG is offline   Reply With Quote
Old 11-13-2019, 02:11 PM   #10
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,461 Times in 1,442 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Lookup Fields are evil? Or is this ok? Need a little guidance

Quote:
Originally Posted by Anthony.DG View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Anthony.DG (11-13-2019)
Old 11-13-2019, 02:55 PM   #11
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,478
Thanks: 0
Thanked 577 Times in 573 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Lookup Fields are evil? Or is this ok? Need a little guidance

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 11-13-2019, 03:07 PM   #12
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,719
Thanks: 105
Thanked 1,513 Times in 1,426 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Lookup Fields are evil? Or is this ok? Need a little guidance

Quote:
Originally Posted by June7 View Post
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.

Quote:
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.
Galaxiom is offline   Reply With Quote
Old 11-13-2019, 09:34 PM   #13
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,316
Thanks: 15
Thanked 1,601 Times in 1,520 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Lookup Fields are evil? Or is this ok? Need a little guidance

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
Anthony.DG (11-15-2019)
Old 11-13-2019, 10:20 PM   #14
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,478
Thanks: 0
Thanked 577 Times in 573 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Lookup Fields are evil? Or is this ok? Need a little guidance

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 11-13-2019, 11:05 PM   #15
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,316
Thanks: 15
Thanked 1,601 Times in 1,520 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Lookup Fields are evil? Or is this ok? Need a little guidance

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

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup fields v lookup tables dscudder Tables 17 08-22-2013 10:45 AM
Evil of lookup tables? Johnny Tables 5 04-06-2011 03:15 PM
Question How do I eliminate my evil Lookup Fields redpenner General 1 07-28-2010 10:19 PM
Evil, EVIL report ! Guillaume777 Reports 5 05-09-2006 05:47 PM
Lookup Fields using Lookup Wizard nathangoldhill Tables 1 03-06-2005 05:37 PM




All times are GMT -8. The time now is 02:12 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World