LookUp? What Do I Use Instead?

J

jesusoneez

IT Dogsbody
Local time
Today, 19:55
Joined
Jan 22, 2001
Messages
109
I've been using Access for a fair while now, but have only recently come across the community consensus that designing a table with lookups are "bad".

Correct me if I'm wrong, but the only real reason I can find for "in table" lookup fields being bad, is that it's an Access only thing, and changing the database to another format renders the offending field useless (such as upscaling to a SQL backend.

Fair enough. All I've read sais lookups are bad, bad, bad!

None of them say what to use instead.

Now, I use lookup fields to look up data from my normalisation tables, as I'm sure most people do, so I can only assume the alternative to this is as follows.

tblMainData has a text field which used to be a lookup to the Department field of tblNormal_Departments. Now it's a text field, it contains no data.

I create a form for data entry into tblMainData. The record source for this form in tblMainData. I create a combo box, with a record source of tblNormal_Departments, and tell it to store the selected item in the Department field of tblMainData.

Is this the workaround (or the way it should be done)?
 
If I've read your question correctly; I feel that populating a fld (doing a 'look-up') from a seperate table is fine if you build it all from scratch, it's the 'Lookup' tab thing you see in table design view that may cause problems if you upgrade, etc.
 
Yup, I was referring to the Lookup tab during table design. So, generally what I said is the way to go?

Sorry about the double post, I didn't think it posted first time...
 
In my opinion it's the part of the essence of databases. :)
 
Thanks, just wanted to be clear on what I should do instead.

That's another one off the evil's of the Lookup tab.
 
it isnt so much that designing the lookup within the table design is wrong, just that it is anti-relational. if you design the table in that way, the table diplays the referenced value of the lookup, rather than the value actually held in the table, and automatically generates a combo box if you put the field on a form. It can become very hard to SEE the true value stored in the table, which may be confusing. As any lookup relationship can easily be designed for a form control, there is no need to setting the lookup at design time.

[edited some typos]
 
Last edited:
Let me summarize. Lookups at the table level are bad. Lookups on forms are good. In addition to what gemma mentioned, the real problems arise once you start to write queries or vba. It is never clear whether you need to use the numeric ID or the text description and some things just don't work until you remove the lookup at the table level. Captions can also cause problems with queries and VBA since you can't get rid of them by aliasing a field and again once you get into queries and VBA, they are more trouble than they're worth. At best they save a couple of seconds when creating forms and reports. They are no inconvienence to the user because the user NEVER looks at tables or queries so your properly formatted names should not confuse him.

So, yes you should be creating proper relational tables. Yes you should be storing the ID rather than the text field. Yes you should use lookups on your forms. You just shouldn't use lookups at the table level. This is a minor inconvienence to you as a developer. If you want to see the text value as well as the numeric value, you'll need to create a query to join to the lookup table rather than just opening the main table. It is transparent to the user because the user should NEVER be looking at tables or queries, he should be looking only at forms and reports.
 
I think that full explanation is crystal clear Pat.
 
As a matter of opinon - :)...

There are times when I think it's fine to have a simple look up table but not do all the store the fk id instead of the text thing and create a relationship, etc. Say you simply want to have the user select red, blue or green. You could put it in a table to be used as a row source and simply store red, blue or green in the main table. It depends on a lot of things...
 
Depending on what version of Access you are using, that may or may not be a problem. The issue with hardcoded value lists is their propagation. If you change the list in the table, will the changes appear in your forms? If you change the list in your forms, will the two be forever out of sync? Access 2007 handles this better than earlier versions of Access. As long as you define the list at the table level, Access will keep your forms in sync.
 
The way I'm doing it now is having my normalisation tables set up as say;

UID (PK) Department
1 Science
2 Math
3 Arts

Any "Department" field in any other tables will be set as text fields, and a form set up to use a combo box to "look up" the data from the department normalisation table.

That's generally how I'm working it for all my normalised data, and it works well.

I can't say that I've physically set up any relationships... seems to work fine as it is.

EDIT: As for data synchronicity, I limit the combo boxes to the given values. However, I usually supply a link for advanced users that'll take them to a form for adding normalisation data. Reopening the data entry form after that will obviously then include the added data in the combo box.
 
If you have an ID in the table, THAT is what should be stored as the foreign key. It is wrong to store the text value. It is NOT wrong however to create the table with a single text column. In that case, you would need to specify CascadeUpdate when you define RI.
 
Pat Hartman said:
Yes you should be storing the ID rather than the text field. Yes you should use lookups on your forms. If you want to see the text value as well as the numeric value, you'll need to create a query to join to the lookup table rather than just opening the main table.
Click to expand...

Hi I got told off for my lookups in my main table. So I removed them all but now I don't know how to rebuild the input form- do I just add a combo box and make the source the relevent data table? and secondly I had another form which had the main table as a sub-sheet so you could see but not edit all the data. Except I did as told and made the mainform onyl contain the ID numbers. how can I make it so that you see the text not the number. you say query- I'm not very good, exactly what query and how do I embed it?
Hope that makes some sense! Thanks!
 
thanks, yes I think it's a much talked about topic.
Right I've read all those posts which has answered my first question- I manually need to create combo boxes which is fine though annoynig (when I first created my db I used these then discovered lookup and deleted them all. grr.)
but I'm still not sure how to display the FKs in the main form as text. Is it possible or instead of having that subdata table do I have to create a locked form or maybe report- ah, the sub table was so easy :(
 
Last edited:
the fk in the main form as text you get by changing the 'column widths' of the combo box. simply make the ID column "0cm", and the others wide enough to accomodate the text. a combo box will only display the first visible column, though it will show you all the columns when you press the droparrow (assuming you have made the column widths and the list width big enough to accommodate).

as for using lookups to enter data - sounds like you were using the tables themselves to enter the data - which is seen as a rather large faux pas - tables are only for storing data, forms for data entry and display, and reports for data display and print.

there was no reason to delete the combos you made already on your forms - unless you were changing your table structure. and one more thing - don't forget to go to your relationship window to assign the correct relationships between your fk and pk's in your tables.

post back if you need more assistance.

you're doing well :)
 
thank you thank you thank you- you're also one of the few people who's nice when telling me I've done everything wrong! Right now it's the middle of the night though I'm still at work unfortunately this computer doesn't have access but I'm thinking if I post my db up first thing tomorrow, that way it might be easier for you to highlight my mistakes. until tomorrow... :D
 
Hello I am new to this thread, but I have been reading up on lookup fields in tables and want to remove some of mine. I have heard that removing them can cause bugs and lots of updating of forms and queries. I dont mind replacing the fields in the forms and queries because they simply wont work anymore. Is there any other considerations like data loss which would be much more serious. I will probably try turning the lookup fields to text fields where the looked up table is a simple 1 field table without a subquery that hides the ID. I am working in Access 2007
 
Last edited:
bali3377 said:
Hello I am new to this thread, but I have been reading up on lookup fields in tables and want to remove some of mine. I have heard that removing them can cause bugs and lots of updating of forms and queries. I dont mind replacing the fields in the forms and queries because they simply wont work anymore. Is there any other considerations like data loss which would be much more serious. I will probably try turning the lookup fields to text fields where the looked up table is a simple 1 field table without a subquery that hides the ID. I am working in Access 2007
Click to expand...
You shouldn't loose any data when you remove lookup fields if you just change the format of the field.
 
You must log in or register to reply here.

Similar threads

A
Solved Combo/List Box Lookup Add Item
Replies
11
Views
777
AngelSpeaks
A
E
Issues creating a lookup in a table
2
Replies
24
Views
1,029
Mike Krailo
Mike Krailo
S
how to show picture on form by lookup from other table field
Replies
7
Views
523
Sokkheng
S
N
How to view/amend Lookup Values in Access 2016
Replies
1
Views
332
arnelgp
arnelgp
E
Solved DLookup & Concatenate
Replies
11
Views
468
cheekybuddha
cheekybuddha

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom