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 autonatically generates a combo box if you but 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 realtionship can easily be designed for a from control, there is no need to setting the lookup at design time.

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.

Thanks all for the concise information.

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.