Storing Combo Box Values

[horton_brian]

Using the wizard I've ended up with two different ways to handle the Combo Box list of values for two fields called 'Team Involved' and 'Role of Witness'; both use a separate lookup table to store the value list. When the table is opened in Access, in one case the field stores the id of the chosen value, in the other it stores the value itself, but the two are the opposite way round to what I'd expect from the field's data type:
1) The Team Involved Data Type is Number with Lookup Row Source Type 'Table/Query' and Row Source:
SELECT [Team Involved Lookup].ID, [Team Involved Lookup].[Team Involved Lookup] FROM [Team Involved Lookup] but this field appears to store the full text of the team name;
2) The Role of Witness Data Type is Text with Row Source Type 'Value List' and Row Source 'Role of Witness Lookup' but this field appears to store the id number of the team name;
Both work OK so far, but what are the pro's and con's of each method, how did they end up different and does Team Involved really store the text string name when it's a Numeric field, or is the Access Table Viewer doing a lookup under the covers?
Brian

 

[namliam]

You should use 1, not 2, if the values ever change you have to do so INSIDE the combobox which is a nightmare if used in multiple places.

The reason you see the "full text" in the table is probably because that field is (in the table) setup as a lookup field, which is pretty bad...
Because dispite it showing the value, it is storing the ID. Thus a search/query on this value will fail because it isnt 'actually' there.

 

[horton_brian]

Thanks
The 'Team Involved' field does store the numeric team id because I can query it as:
Incident AS I LEFT JOIN [Team Involved Lookup] AS T ON I.[Team Involved]=T.ID)
and get back the team name, so the Access Open Table UI must be doing the lookup. Are you saying that for 'Role of Witness' the table's actually storing the text of the role name and the Access Open Table UI is doing a lookup and showing it as the numeric role id?
Brian

 

[namliam]

No, ...

The number field is the number stored and displayed...
The 'text' field is displaying text but storing numbers....

 

[horton_brian]

Sorry, I must be being thick but:
The number field (Team Involved) stores a number but displays the corresponding looked up text in the Access Table viewer and the looked up text in the form Combo which has Control Source
Incident.Team Involved
and Row Source
SELECT [Team Involved Lookup].ID, [Team Involved Lookup].[Team Involved Lookup] FROM [Team Involved Lookup];
The text field (Role of Witness) stores a number (presumably as a string)and it shows as a number in the Access Table viewer but as the corresponding looked up text in the Form Combo which has Control Source Incident.Role of Witness and Row Source
SELECT [Role of Witness Lookup].ID, [Role of Witness Lookup].[Role of Witness] FROM [Role of Witness Lookup];
Is that what you're saying?
Brian

 

[namliam]

Sorry, I must be being thick but:
The number field (Team Involved) stores a number but displays the corresponding looked up text in the Access Table viewer and the looked up text in the form Combo which has Control Source
Incident.Team Involved
and Row Source
SELECT [Team Involved Lookup].ID, [Team Involved Lookup].[Team Involved Lookup] FROM [Team Involved Lookup];

Yes, it is storing the number ID... But displaying [Team involved lookup] field

The text field (Role of Witness) stores a number (presumably as a string)and it shows as a number in the Access Table viewer but as the corresponding looked up text in the Form Combo which has Control Source Incident.Role of Witness and Row Source
SELECT [Role of Witness Lookup].ID, [Role of Witness Lookup].[Role of Witness] FROM [Role of Witness Lookup];
Is that what you're saying?
Brian

Well if your [role of witness] is (supposed) to be a lookup field, no sence in making a text field, ID == number then this field should be number too.

What determains if the "lookup text" is displayed or not is the fact if the column in the table has been setup as a lookup field or not.

 

[horton_brian]

OK, I've set all the ID fields in Incidents to Number and used the wizard to make them all display the looked up text in the Table view. Thanks for your help.
Brian

 

[boblarson]

OK, I've set all the ID fields in Incidents to Number and used the wizard to make them all display the looked up text in the Table view. Thanks for your help.
Brian

NOOOOOOOOOOOOOOOOOOOOOO! Do NOT use lookups at Table level. See here for why. You can use them on a Form but not in table.

 

[horton_brian]

So what you're saying is:
I should, as I already do:
1) Store the lookup values in a separate table and use this table to populate the combo box on the form
But not:
2) Use the lookup wizard in the Table Designer to set the secondary key field in my incident form as a Lookup, just leave it as a Number and use the Tables visual relationship editor to set one to many relationships with forced RI between each lookup table and the Incident table, as I would for any other relational DBMS?
Right?
Brian

 

[boblarson]

Yes, that is correct. You store the ID in the table you are storing data in, you have a table of lookup values which has the description and ID and then when you use the combo on the form to select the values you set the row source to be the lookup table which has both ID and Description, set the column count to 2, set the column widths to 0";2" which then (if you have the ID as the first column in your row source query) will show the description but store the ID.

 

[Ani]

Yes, that is correct. You store the ID in the table you are storing data in, you have a table of lookup values which has the description and ID and then when you use the combo on the form to select the values you set the row source to be the lookup table which has both ID and Description, set the column count to 2, set the column widths to 0";2" which then (if you have the ID as the first column in your row source query) will show the description but store the ID.

Hi Bob
trying to get my head around the rest of the combo box set up for my Titles choices Mr, Mrs etc. Do I understand the above -
the values that are wanted on the form (within the combo box) are stored in a table with just ID as one field?
So I need two separate tables PK ID and a Field 'Title' with data (values) 'Mr, Mrs etc'
And a table with PK ID to store them?
Cheers
Ani

 

[boblarson]

Hi Bob
trying to get my head around the rest of the combo box set up for my Titles choices Mr, Mrs etc. Do I understand the above -
the values that are wanted on the form (within the combo box) are stored in a table with just ID as one field?
So I need two separate tables PK ID and a Field 'Title' with data (values) 'Mr, Mrs etc'
And a table with PK ID to store them?
Cheers
Ani

If you have the salutations, you can just set up a table like this:

tblSalutations
SalutationID - Autonumber (PK)
SalutationText - Text

that would store the Mr., Mrs., Miss, Ms., Dr., etc. and each would have the ID automatically added to it when you add them to the table

then, in your table where you have their names and such (example to follow), you would store that ID by way of a combo box on the form which has the rowsource set to that tblSalutations table and the FK field bound to that combo box.

tblPersons
PersonID - Autonumber(PK)
SalutationID - Long Integer (FK)
FirstName - Text
LastName - Text
Address - Text
City - Text
Region - Text
PostCode - Text

etc.

 

[Ani]

If you have the salutations, you can just set up a table like this:

tblSalutations
SalutationID - Autonumber (PK)
SalutationText - Text

that would store the Mr., Mrs., Miss, Ms., Dr., etc. and each would have the ID automatically added to it when you add them to the table

then, in your table where you have their names and such (example to follow), you would store that ID by way of a combo box on the form which has the rowsource set to that tblSalutations table and the FK field bound to that combo box.

tblPersons
PersonID - Autonumber(PK)
SalutationID - Long Integer (FK)
FirstName - Text
LastName - Text
Address - Text
City - Text
Region - Text
PostCode - Text

etc.

Ok, Thanks, that makes more sense now. I couldnt work out if I had two tables how they would be connected. I forgot about the FK bit! I was searching threads to make sure I could store the info!
Brilliant...Im getting there...slowly!
Ani

 

[Steve C]

Hello Bob

Your link to the Evils of Look Ups a table level is, of course, completely right. Nevertheless, I missed the significance of it.

In the past my databases have all been very simple affairs - just for me - and Look Ups Fields in Tables work fine at my (very low) Level.

It's only now that I am trying to create a slightly more sophisticated database that Look Ups in Tables are causing a problem.

Thanks to Brian and Ani for asking your questions and most of all for explaining how your understanding of Bob's answer.

Tonight, I've completely started afresh with my db. Tomorrow I'll create some Forms (with Combo Boxes in there) to enter data into the Tables. I'll poke around on here for how to.

Best wishes Gents

 

[Ani]

Hello Bob

Your link to the Evils of Look Ups a table level is, of course, completely right. Nevertheless, I missed the significance of it.

In the past my databases have all been very simple affairs - just for me - and Look Ups Fields in Tables work fine at my (very low) Level.

It's only now that I am trying to create a slightly more sophisticated database that Look Ups in Tables are causing a problem.

Thanks to Brian and Ani for asking your questions and most of all for explaining how your understanding of Bob's answer.

Tonight, I've completely started afresh with my db. Tomorrow I'll create some Forms (with Combo Boxes in there) to enter data into the Tables. I'll poke around on here for how to.

Best wishes Gents

Good Luck Steve C...Im still learning and practising, this is the first time Ive set a db up and even the 'language' and terminology confuses me. The forum is great, everyone is really helpful.
Ani

 

[Ani]

NOOOOOOOOOOOOOOOOOOOOOO! Do NOT use lookups at Table level. See here for why. You can use them on a Form but not in table.

Quick question Bob, that may have an answer I wont understand. I understand that setting tables up with lookups will cause problems but why does Access allow you to do it? With most stuff that Im experimenting with you get error messages or messages about things not being 'enforced referentially' etc. Is it a programming type thing from when Access was first invented?
Ani

 

[boblarson]

Quick question Bob, that may have an answer I wont understand. I understand that setting tables up with lookups will cause problems but why does Access allow you to do it? With most stuff that Im experimenting with you get error messages or messages about things not being 'enforced referentially' etc. Is it a programming type thing from when Access was first invented?
Ani

The true answer is that Microsoft has not been interested in setting up Acccess to be "best practices compliant." They keep trying to put in things which will look cool, appeal to someone who doesn't use the program yet but may and so they do what they think will help them even though the MVP's have told them time and time again that they are just making it worse on people because if something is there, new people are almost assuredly going to use it. But just because its in there doesn't make it good to be there. That is the reason.

 

[Ani]

The true answer is that Microsoft has not been interested in setting up Acccess to be "best practices compliant." They keep trying to put in things which will look cool, appeal to someone who doesn't use the program yet but may and so they do what they think will help them even though the MVP's have told them time and time again that they are just making it worse on people because if something is there, new people are almost assuredly going to use it. But just because its in there doesn't make it good to be there. That is the reason.

OK, sounds pretty much just typical MS marketing ploys. As you can do these things on tables I wrongly assumed it was OK to do so. As you say, it really doesn't help new users!
Ani