Solved New guy with a table structure question... (1 Viewer)

[Kyp]

Hello gang

I have started an access project and after searching the forum, I have a couple questions on table structure.
Questions:

I've read in several threads that I should NOT use any type of "Lookup" in a table structure. No "Combo Boxes", or "Value List", period.
I understand but why?


Not using a “Lookup” in table1, I will create table2 with the lookup values I want to store in table1. Utilizing a form, I create a combo box. The “Control Source” of the combo box will be the field in table1 that I want to put the data into, and the “Row Source” will be a query based on the values I entered in table2.

Am I correct thus for?


Assuming this is the correct way to format a lookup. What is the best and easiest way to enable the user to update this list without giving the user access to the table holding the values, a separate form?


If all this is correct, I will have lots of tables storing lookup values and lots of forms for the user to update these lists when needed.

Is there an easier, better, or cleaner way to accomplish this task?

Sum it all up example:

In tbl_Equipment contains the field “Status”. The data type is “Short Text”.
In tbl_Status contains the StatusID field, (AutoNumber) and the filed “Status” with the data type as “short Text” that will contain the list of statuses. The frm_Equipment based on tbl_Equipment will contain a combo box. The control source of the combo box will be from tbl_Equipment, “Status” field. The row source is the query based on the tbl_Status for the values.

 SQL: SELECT DISTINCTROW Status.* FROM Status ORDER BY Status.Status;)

I will have a multitude of these lookup(s) for various form combo boxes.
Is there a better way as to not create a table for each lookup value and a form for each look value table for updating each list?

Stand by! More questions to come!

-Kyp

 

[theDBguy]

Hi. Have you seen this?

The Access Web - The Evils of Lookup Fields in Tables

 

[jdraw]

Several articles and tutorials on Database Planning and Design. I recommend the tutorials from RogersAccessLibrary mentioned in that link.

 

[MajP]

For very simple lists where I store just the value and not a related key I often make a single table.

TblLookupItems
LookupID
LookupCategory
LookupDescription
LookupSortOrder

So I can have all kind of random things

Status Operational
Status Broken
Status In Repair
Status ....
Temp Hot
Temp Warm
Temp Cold
Size Small
Size Medium
Size Large

Then on a form if I need to pick the size in a drop down I select from tblLookuItems WHERE Category = "Size" order by LookupSortOrder.

I include a sort order because with things like size you do not want to sort alphabetical but probably extra small to extra large. If at a later date you add another size you want to be able to define how they sort.

 

[The_Doc_Man]

The issue isn't a lookup per se but what it implies and the way it interacts with other structures. A lookup field often invokes permutation joins if the query is not properly qualified and often confuses the difference between a code and text equivalent for that code.

A lookup on a FORM, however, doesn't complicate the table that is the form's record source. So when we say "avoid table lookups" that is not the same as saying "use lookup tables." Because a lookup table is just a table for which a JOIN could profitably be made to look up something for a query, which DOESN'T permanently affect the lookup table. Stated another way, there is significantly less baggage in an externally JOINed lookup table. The other articles offered by my colleagues will explain that, too.

 

[isladogs]

Another link explaining issues with lookup fields Table Lookup Fields - Mendip Data Systems

 

[Kyp]

Thanks guys for all the info.
I am reading over all the links now.
Cheers!
-Kyp

 

[Kyp]

I've read over the info you guys shared and very informative to say the least.
Some things are understandable while others are a little over my head at this point.

I'm building a DB for 2 reasons.
1. To learn the fundamentals of relational database as I truly enjoy it!
2. To help myself organize and control data that I would usually put into separate spread sheets for different items. I have a hundred different excel books that I can condense into one access database.

I will design the table structure this morning and start a new thread pertaining to the build of the db, and mark this thread as solved.

The new thread I will start (if okay?) will go from the inception of the db to the final working product.
That way, I can keep all my info in a single thread and hopefully, someone else will be able to benefit from my journey!

You guys have really opened my eyes to the proper design and normalization of tables.
I would like to thank each of you for sharing the info with me!

The journey continues!

-Kyp
73

 

[jdraw]

You will experience database design by working through one of the tutorials from RogersAccessLibrary.
He has a procedure that will take you from problem description to normalized tables, and you can use that procedure with any database. May be the best 30-45 minutes you will spend.
Class info system

 

[The_Doc_Man]

That way, I can keep all my info in a single thread and hopefully, someone else will be able to benefit from my journey!

It's your thread, but generally after enough stuff gets tacked onto a thread, people start ignoring it. You can always find your own threads if you post multiples, because the forum has a specific command to show you your posts. So finding all of your info is almost trivially possible.

 

[June7]

Advise not to build lookup fields in tables because I prefer to see the real stored value when I view tables. Build comboboxes and listboxes on forms. Use queries joining tables to view related data. More info http://access.mvps.org/Access/lookupfields.htm and to be fair, the other side https://improvingsoftware.com/2009/10/02/blog-response-lookup-fields-in-access-are-evil/

 

[Pat Hartman]

Some apps require more lookups than others and lookups should almost always be managed by the user community rather than the developer. You are developing for yourself but there is no reason to learn what would work in a larger, more complex app. More than 40 years ago (well before I fell in love with Access) I was developing a large COBOL/IMS (hierarchical database) that was for an insurance company and so includes tons of codes. It became burdensome to create new forms and tables to hold each list separately so I came up with a consolidated solution that I have implemented numerous times since then with the current version being in Access with tables in whatever BE the client company wanted. This mini-app is intended for use on any simple lookup list. The table includes an ActiveFlag so you can keep inactive entries and have the combos work but also add code to your form to prevent someone from choosing an inactive value for a new record. The table also includes a field that indicates who has permission to update the table. Sometimes any user can be allowed to add items to a list but more commonly, updates should be restricted to users designated as administrators. Rarely, you'll run into some code type where the programmer needs to control the values because new items would require special programming and changing the names of items would never be allowed.

I've attached the database.

 

[Kyp]

You will experience database design by working through one of the tutorials from RogersAccessLibrary.
He has a procedure that will take you from problem description to normalized tables, and you can use that procedure with any database. May be the best 30-45 minutes you will spend.
Class info system

I've just downloaded it and will start on it now.

Thanks!
-Kyp
73

Some apps require more lookups than others and lookups should almost always be managed by the user community rather than the developer. You are developing for yourself but there is no reason to learn what would work in a larger, more complex app. More than 40 years ago (well before I fell in love with Access) I was developing a large COBOL/IMS (hierarchical database) that was for an insurance company and so includes tons of codes. It became burdensome to create new forms and tables to hold each list separately so I came up with a consolidated solution that I have implemented numerous times since then with the current version being in Access with tables in whatever BE the client company wanted. This mini-app is intended for use on any simple lookup list. The table includes an ActiveFlag so you can keep inactive entries and have the combos work but also add code to your form to prevent someone from choosing an inactive value for a new record. The table also includes a field that indicates who has permission to update the table. Sometimes any user can be allowed to add items to a list but more commonly, updates should be restricted to users designated as administrators. Rarely, you'll run into some code type where the programmer needs to control the values because new items would require special programming and changing the names of items would never be allowed.

I've attached the database.

Some apps require more lookups than others and lookups should almost always be managed by the user community rather than the developer. You are developing for yourself but there is no reason to learn what would work in a larger, more complex app. More than 40 years ago (well before I fell in love with Access) I was developing a large COBOL/IMS (hierarchical database) that was for an insurance company and so includes tons of codes. It became burdensome to create new forms and tables to hold each list separately so I came up with a consolidated solution that I have implemented numerous times since then with the current version being in Access with tables in whatever BE the client company wanted. This mini-app is intended for use on any simple lookup list. The table includes an ActiveFlag so you can keep inactive entries and have the combos work but also add code to your form to prevent someone from choosing an inactive value for a new record. The table also includes a field that indicates who has permission to update the table. Sometimes any user can be allowed to add items to a list but more commonly, updates should be restricted to users designated as administrators. Rarely, you'll run into some code type where the programmer needs to control the values because new items would require special programming and changing the names of items would never be allowed.

I've attached the database.

Thank you ma'am, and sorry about the confusion!

 

[The_Doc_Man]

Kyp - Pat's used to it, because when I made the mistake, she forgave me too. However, "Thank you sir" is inappropriate. "Pat" is decidedly female in this case.

 

[Kyp]

Kyp - Pat's used to it, because when I made the mistake, she forgave me too. However, "Thank you sir" is inappropriate. "Pat" is decidedly female in this case.

Oof I am sorry, I did not realize.
thanks for letting me know!

 

[Pat Hartman]

Being assumed to be male doesn't offend me. Just don't call me Phil. It is the way the world works and being offended by it won't change a thing so NP I give you all dispensation to call me whatever you want. Just don't call me late for dinner

 

[Kyp]

Being assumed to be male doesn't offend me. Just don't call me Phil. It is the way the world works and being offended by it won't change a thing so NP I give you all dispensation to call me whatever you want. Just don't call me late for dinner

You’re so awesome!

 

[Pat Hartman]

You’re so awesome!

Flattery will get you anything

 

[gemma-the-husky]

"Should" is not must.

The thing is,(I think) it's nice for a developer to be able to look at a table and see the real value that's stored in the table. The lookup value masks this by showing you the result of the lookup. Often the lookup that's stored in the table isn't the one you want anyway when you come to add the field to a form. It's just easier to leave everything until you come to develop the form, or the query. Users will never be inconvenienced by this, as they shouldn't be looking at the tables directly. So really it's whatever you are happy with as the developer.

Another similar thing is the "caption" property for a field in a table. You can change the real underlying name in the table by using a "caption", so that forms and queries show the caption, rather than the real field name. It's horrendous when you try to debug a database where captions have been used. It can be really hard to identify the real source of a field name you are seeing, when that name is a "caption". It's just not obvious or apparent that you are seeing a caption. To me, captions are a bigger non-no than lookup values.

 

[Pat Hartman]

To me, captions are a bigger non-no than lookup values.

I couldn't agree more having been on the wrong side of this awful feature in my early days when I was trying to fix a database created by someone else. It was even worse in the past because sometimes Access used the caption and sometimes it use the actual name so the "feature" was even more destructive.