Solved Setting up Tables (1 Viewer)

[Teri Bridges]

I have taken three online courses, each instructor has done things differently, without any clear explanation. I was wondering if you guys might help provide clarity.
When setting up the tables why do you not use the lookup wizard for a field so that it will populate with the value as opposed to the ID number (primary Key)?
The tables are more user-friendly when they display the value for the field as opposed to the ID number.

 

[plog]

The tables are more user-friendly

That's not a trait tables should have. Users have no need for direct table access. They should be accessing data via forms and reports. And for a developer--its a huge pain when people do that to their Access databases. I want to see actual values not a related lookup value in the table.

Additionally, a number takes up less space than a text value so when you use numeric foreign keys instead of text values you make your database smaller. Lastly, it helps keep data integrity. Suppose you have a table of ingredients, 1 is Salt, 2 is Peper, 3 is Cinnamon, etc. . Then in a recipe table you have an ingredients field and you use those values and populate 100,000 rows. But now you look back and and realize I misspelled 'Pepper'. If you stored 'Peper' in the ingredients table you must now update every record to the correct spelling. If however you stored just the number of the ingredient in the Recipe table you only have to correct 'Pepper' in the ingredeinets table because in Recipes you have stored the value 2 and that value now relates to the correct spelling.

 

[pbaldy]

Also:

The Access Web - The Evils of Lookup Fields in Tables

 

[The_Doc_Man]

I'm with plog. Tables should NOT be user friendly because they are supposed to be COMPUTATION friendly. Forms and reports are how you make something user friendly.

Using a lookup field in a table looks easy until you finally graduate to doing something more advanced - at which time, a lookup field comes back to bite you. Lookup fields are based on "hidden" tables that become very difficult to trace back. You must understand that there are two cases to consider in a database.

1. This database is for you and you alone, no other users will ever see it. Then it might not matter, appearance-wise, what you do. But it will still matter on a technical level as your database evolves.

2. This database is being prepared for multi-user accessibility. Such databases should NEVER EVER IN A MILLION YEARS (sorry for yelling) show users anything but forms and reports and that variant of a form called a switchboard or dispatcher. You cannot show the "innards" of your database to the world because if you do, you have NO security and NO WAY to control what is or isn't done. Even the best-intentioned users cannot be fully trusted ... because they are human and make mistakes. The moment even ONE user gets into a table and hits "DELETE" instead of "PAGE DOWN" you just lost data in a way that will be hard if not impossible to recover.

 

[Pat Hartman]

When setting up the tables why do you not use the lookup wizard for a field so that it will populate with the value as opposed to the ID number (primary Key)?

Table level lookups are a crutch for beginners. Once you pass the rank novice stage and graduate to queries and code, they are more trouble than they are worth and they have a HUGE downside. When you open a table in data sheet view you ALWAYS want to see the actual data that is stored in the table. Table level lookups hide that from you. You will be forever after confused about whether you are working with the CustomerName or the CustomerID.

If you want to see the CustomerName rather than CustomerID in any query, just include a join to the customer table and select whatever columns you want to see.

 

[CJ_London]

why do you not use the lookup wizard for a field so that it will populate with the value as opposed to the ID number

The point is it is not populated with the value. It is hiding the actual value.

 

[Teri Bridges]

Table level lookups are a crutch for beginners. Once you pass the rank novice stage and graduate to queries and code, they are more trouble than they are worth and they have a HUGE downside. When you open a table in data sheet view you ALWAYS want to see the actual data that is stored in the table. Table level lookups hide that from you. You will be forever after confused about whether you are working with the CustomerName or the CustomerID.

If you want to see the CustomerName rather than CustomerID in any query, just include a join to the customer table and select whatever columns you want to see.

I just found this out. I was trying to put some calculations in my report and found that the look-ups I had in the table were giving me fits. i have moved all the look-up fields I need to the form. I just spent 2 hours adjusting the table. It is hard for me to wrap my mind around the table view because I see the ID number when I look at it. I want to see John Smith in the client field, not client 1. lol I will learn. everyone here has been most helpful.

 

[Teri Bridges]

Also:

The Access Web - The Evils of Lookup Fields in Tables

Thank you for the link. I have saved it for future reference.

 

[Pat Hartman]

It is hard for me to wrap my mind around the table view because I see the ID number when I look at it.

Create a query that joins the two tables so you can pull in the name and use that. Or use the application interface to review data is that is what you are doing.