Combo list query

Question

I have an Access database form where I use a combo box to select a name from an alphabetical list. The combo box has two columns and the control source is a query, again with two columns: ID number and name. The first column of the combo box is the ID number, set at 0 width and a name column set at 6.5 cm width. When I scroll through the box I only see the name column, which is correct. But in my data table only the ID number is stored. What am I doing wrong? Only the name should be stored

Answer 1

You might like to take a look at Normalization.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have trouble downloading a specific file, clicking on the 'Download' button at the top of the page while no files are selected should download a zip file of all files in the folder, from which you should then be able to unpack the relevant file.

This little demo file provides as simple as possible, but no more so, an introduction to the subject of normalization by decomposition.  In the first instance concentrate on the first three normal forms, in particular Third Normal Form (3NF), which is pertinent to your issue.  Note how only CustomerID is stored in a column in the Orders table, as my colleagues here have explained.

The demo includes an example of a user interface, made up of a form and subform, for entering data in this context.  As you'll see the combo box for selecting a customer shows the names but is bound to the CustomerID foreign key column in Orders, just as in your case.  To insert a new customer the form includes a button which opens a dialogue form, rather than allowing the user to type a new name into the combo box and using code in the control's NotInList event procedure to insert a new row into the Customers table.  This is necessary because personal names can legitimately be duplicated.

Answer 2

First, lets correct your terminology.

"the control source is a query". A ControlSource is eiother a field in the forms's underlying RecordSource or an expression that displays a value. What you are referring to os the RowSource, which is a property of a combobox that controls what is displayed in the list.

Second you said; "But in my data table only the ID number is stored. What am I doing wrong? Only the name should be stored" No the name should NOT be stored. So you are doing nothing wrong. In a relational database, Data should exist in one table and one table only. So the name should only exist in the table where the people are stored. The ID field is what is stored as a Foreign Key so you can relate the record to the people table and pull whatever info you need about the person by way of a query with a join or similar methodology.

Answer 3

Perhaps you could invest some time learning more about the use of Primary and Foreign Keys in a relational database as a way to better understand why this is the proper and expected behavior. There are many good discussions on Normalization, which is the basis on which tables are designed and built in a relational database.

Answer 4

That is the expected behavior. Only the value of the Bound Column of the combo box (by default the first column) is stored in the record source of the form. And that is perfectly OK - there is no need to store the name in the table: it would be redundant information, since the name can be derived from the ID. You can always use a query to return the name corresponding to the ID.

Answer 5

Thank you gentlemen. My issue is now fully resolved