Lookup Tables

[ladyrider696]

I have a lookup table that have 2 fields (service level and service level description). If I select service level in the lookup table I want the service level description to automatically be inserted in the service level description field in my table.

 

[boblarson]

That is an erroneous design.

First, you should not be using lookups at table level. See here for why.

Second, if the description is already in the lookup table you don't store it in the other table. You only store the ID. Then you use a QUERY when you want to use the description. That is part of the NORMALIZATION process.

 

[mcgilla]

That is an erroneous design.

First, you should not be using lookups at table level
Second, if the description is already in the lookup table you don't store it in the other table. You only store the ID. Then you use a QUERY when you want to use the description. That is part of the NORMALIZATION process.

Bob,

I found on your website your reasons for not doing lookup at the table level, which unfortunately I did 5-6 years ago when creating several databases that are currently used. I'm now starting a task to consolidate our databases with my own login security but have all of these tables with lookup at the table level.

Hopefully an easy enough task to change all of that in the back end database.

Should the relationships be built in the back end, front end or both?

Ed

 

[boblarson]

You can remove them from the tables by doing this. Most things should be fine but you might need to add in the lookup table for the descriptions on some queries, forms or reports.

Relationships should be defined at the BACK END.

 

[mcgilla]

Thanks. I appreciate the help.