Look Up Combobox

[shamas21]

Hi All

I have a problem with the LOOK UP tab in the Tables Design View.
I want to lookup a column from a table called PRODUCTS that has the information in column 2.

However, when i open my table, the combo box drop down list always has the values of the FIRST column in it.

How can i get the table to lookup the values from column TWO in the PRODUCTS table

Thanks

 

[neileg]

Table level lookups are more trouble than they are worth. Do a proper lookup in a form.

 

[tpeter]

The database will always look up the primary key or unique value. If you want to display the information in colume 2 go to disign view and look at the colums cell and you should see something like 1;0. This is telling you that it is showing 1" width for colume 1 and 0" for colume 2. To fix this all you have to do is change the setting to 0;1" (or however wide you want it). I am not sure about the name in the properties but it you go to the all tab you will be able to find it.

 

[boblarson]

As Neil said, get rid of the lookups at table level. Do it on forms. See here for more info about that:
The Evils of Lookups at Field Level

 

[wazawak]

I make fairly extensive use of lookup fields in my database. Is this a cause for concern? Mainly, I use it to link orders to holidays- a field in my tblOrders table is a lookup field for my tblEvents table, and when entering the relevant event for an order, I just select the value from the lookup. Is this acceptable, or should I engineer a better way?

 

[boblarson]

I make fairly extensive use of lookup fields in my database. Is this a cause for concern?

Yes, it is a cause for concern (reasons given in that link)

Is this acceptable, or should I engineer a better way?

Use forms with combos or listboxes to select, not at table level.

 

[wazawak]

So, if I understand correctly, a table just stores raw data. The entire table structure is just big blobs of information, where a column/table may have a weak, vague relationship to another column/table, but it is certainly not explicit. There are orders, and there are customers. We know in our minds and our hearts that every order has a customer, but all the two tables will say is "These are our orders" "These are our customers," and the relationship between the two tables will say "Every order has a customer." It does not specify which order goes to which customer. Detailed information like that goes into forms and reports.

Please correct me if I am wrong or let me know if I'm right, I sense this is a very important question if I am ever to work with Access again.

 

[boblarson]

No, I don't think you have it. Access stores data in tables (which you cannot view directly). It does know that columns are related to each other if they are in the same table. In fact, if set up with a primary key, Access will know down to the row (record) which data goes together.

However, just building tables doesn't relate them. You do this in the relationships window, or in queries. You tell Access what Primary Key and Foreign Key to link so that it then knows that those two tables are related.

You can't just add data to one table and expect Access to know what data is related. You have to include the foreign key (primary key from another table) in the second table. You do this primarily with forms. You can take information from one table and populate information in another by using a combo box on a form just as if you did it with a lookup field in a table.

The problem with lookup fields in tables especially come to light when trying to do queries, reports, or criteria on those fields as Access displays the text but the real ID which you want to use is obscured and not available unless you add the same field twice, which isn't a good thing.

 

[georgedwilkinson]

You're wrong about how it works. You're right that it's important.

Presumably within a table you will have a column that represents the ID from another table. This is why Access and other RDBMSs are called "relational".

In your OrderHeaders table, you will have a column called "CustomerID". In your Customers table you will have a column called "CustomerID" which is the primary key. In the Access "Relationships" tool you will drag "CustomerID" from Customers to CustomerID in OrderHeaders and tell the tool that you want to enforce referential integrity. This creates what is called a Foreign Key in the "OrderHeaders" table on the column "CustomerID", which you created there.

You can query the OrderHeaders table and find out exactly which customer the order is for, without a Form or Report involved. Forms and reports manipulate and display data in the tables. If the table doesn't have the relationship defined, the Form or Report won't be able to do anything about it.

You should never, ever, manipulate data directly in your table and thus, should never, ever, define a combobox as a column type on the table. Use a form defined as I just described.

This is all conceptual. I would never create an Order system like this, but it points you in the right direction.

 

[Spiritwoman]

Thanks for the link! I needed that info too.