LookUp? What Do I Use Instead?

[jesusoneez]

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]

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]

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]

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

 

[jesusoneez]

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]

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]

 

[gemma-the-husky]

I think that full explanation is crystal clear Pat.

 

[jesusoneez]

Thanks all for the concise information.

 

[KenHigg]

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

 

[jesusoneez]

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.

 

[zebra]

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]

i ran into a similar problem not long ago. here's a http://www.access-programmers.co.uk/forums/showthread.php?t=163373&page=2thread 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]

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]

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]

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]

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]

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.

 

[wiklendt]

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

the lookup fields should be data type of Number, and would store your corresponding primary key (PK; presumably autonumber) field from your related table, creating what is known as a foreign key (FK). you should ideally not use any field other than the autonumber field from the related table to become the foreign key (as a number field) in this table.

e.g., if you want to relate a new order with an item to be ordered, and who's ordering it...

tblOrders (hypothetical)
-----------
OrderID (Autonumber, PK)
OrderNumber (Text)
CustomerID (Number, FK)
ItemID (Number, FK)
OrderDate (Date/Time)
.
.
.

in the example table, the 'foreign' stored data would be CustomerID and ItemID. The IDs are known as "foreign" keys, because although they are not a primary key of this table, they are a primary key of a different table. the primary key of the tblOrders table is OrderID.

this assumes, then, that you have two other tables: tblCustomers and tblItems.

those tables would look something like:

tblCustomers
----------
CustomerID (Autonumber, PK)
CustomerName (Text)
.
.
.

and

tblItems
---------
ItemID (Autnumber, PK)
Code (Text)
Description (Text)
ListPrice (Currency)
.
.
.

now, when you have evil table-level lookups, the data stored in your FKs of your tblOrders table would normally STILL be the FK ID of the corresponding table, BUT the DISPLAY of the data is like a query - you just have to remove the 'combobox' in the lookup tab (i think the only other option is 'textbox' - which is what i think you meant in your post, but i feel i must clarify) of that table field and it will remove that 'display', but keep the ID.

the only time you'll need to update forms or queries is if you've imposed criteria on those tables based on the displayed data rather than the stored data. depending on the size and complexity of your database, you might be lucky to not have to do anything at all...

hope all that makes sense - it's a bit early in the morning still here!

edit: beg yours, there is also a 'listbox' option in the lookup tab for table fields... i've never used this but can only imagine it would cause horrendous headaches downstream.

 

[bali3377]

Thank you Robbie, I believe if lookup fields on tables are removed and put on the queries or forms it speeds up the the database. So I am going to have a shot at it.

 

[bali3377]

Thank you Agnieszka for your indepth response regarding F Keys and Primary Keys.
I started off using just autonumber for ID keys but quickly found them a pain in the neck. For instance
Transactions Table (Main Table)
Payment Method (normalization Table)
If I use the autonumber Id. and the subquery to get the payment method name, Whenever I queried this field I had to go looking for the autonumber id for the method i wanted to query for. So for simple tables I stuck to just the word as the ID and have enjoyed the database better for it.
Do you know the reasons for not using this method, so if I stick with it at least I will be making an informed decision. At some stage in the next year or two I might be moving my database to a server - that might or might not be relevant in this instance.
Thanks again