LookUp? What Do I Use Instead? (1 Viewer)

jesusoneez · Oct 5, 2006

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)?

KenHigg · Oct 5, 2006

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.

jesusoneez · Oct 5, 2006

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

KenHigg · Oct 5, 2006

In my opinion it's the part of the essence of databases.

jesusoneez · Oct 5, 2006

Thanks, just wanted to be clear on what I should do instead.

That's another one off the evil's of the Lookup tab.

gemma-the-husky · Oct 5, 2006

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]

Pat Hartman · Oct 6, 2006

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.

gemma-the-husky · Oct 7, 2006

I think that full explanation is crystal clear Pat.

jesusoneez · Oct 12, 2006

Thanks all for the concise information.

KenHigg · Oct 12, 2006

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

Pat Hartman · Oct 13, 2006

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.

jesusoneez · Oct 13, 2006

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.

Pat Hartman · Oct 20, 2006

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.

zebra · May 12, 2009

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.

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!

wiklendt · May 12, 2009

i ran into a similar problem not long ago. here's a http://www.access-programmers.co.uk/forums/showthread.php?t=163373&page=2 thread which will help you with both normalisation and also (via a link in that thread) with the lookup/combobox scenario.

i also recently took time to explain normalisation to someone else here. start at post #13 and read all the way to the last post on the last page.

HTH

zebra · May 12, 2009

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

wiklendt · May 12, 2009

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

zebra · May 13, 2009

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

bali3377 · Jan 31, 2010

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

Rabbie · Jan 31, 2010

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

You shouldn't loose any data when you remove lookup fields if you just change the format of the field.

LookUp? What Do I Use Instead? (1 Viewer)

IT Dogsbody

Registered User

IT Dogsbody

Registered User

IT Dogsbody

Super Moderator

Super Moderator

Super Moderator

IT Dogsbody

Registered User

Super Moderator

IT Dogsbody

Super Moderator

Registered User.

i recommend chocolate

Registered User.

i recommend chocolate

Registered User.

Registered User.

Super Moderator

Similar threads

Users who are viewing this thread