Lookup filed question?

[exceii]

Hello, can someone please shed some light on something for me? I am having a lot of trouble understanding the workings of a particular lookup field in the template "Sales pipeling" (downloaded from Microsoft Office online (business), when creating a new db in Access 2007).

In the "Opportunities" table there is a column called "Customer" which has a lookup row source defined as "SELECT [Customers Extended].ID, [Customers Extended].[Contact Name], [Customers Extended].Company FROM [Customers Extended] ORDER BY [Customers Extended].[Contact Name];" and is bound to column 1. The "Customer" column also has a n:1 relationship to "ID" in the "Customers" table.

The thing that I can't understand is that even though the datatype for the "Customer" column (question: would you call this a column or a field?) is Number (which makes sence becasue it has a relationship with the "ID" column in the "Customers" table) it displays the "Contact Name" (which is the second column in the lookup combo box) from the "Customers Extended" query. Can someone please tell me why this occurs? The bound column for "Customer" is "1" which is "ID" (as far as I am aware) so why does "Contact Name" display?

Thanks for taking the time to read this long question. I have been trying to work it out for the last few hours and now my head is hurting :/

 

[Alansidman]

If you have the form open in design view, open the properites for the combo box. In the format tab, look at the column widths. You will see that the first item is probably zero. This tells Access to hide the bound column and show the subequent columns. In the row above the widths it will ask how many columns in the lookup.

Alan

 

[exceii]

If you have the form open in design view, open the properites for the combo box. In the format tab, look at the column widths. You will see that the first item is probably zero. This tells Access to hide the bound column and show the subequent columns. In the row above the widths it will ask how many columns in the lookup.

Alan

Thanks for the reply. I am actually working with the "Opportunities" table, not a form. You are correct though, the width for the first column is set to 0 but that only affects the information in the combo box. Once I make a selection from the combo box the information displayed in the field is just the "Contact Name" without the "ID" or "Company". I am unsure where the configuration is to make this happen. As the datatype for this column/field is Number I am assuming that a number value (ID) is actually being stored in the field and Access is using that information to display the "Contact Name". I am just unsure why it does this as I cannot find the options to set this.

 

[speakers_86]

Most on this forum will tell you not to use lookups. They are counter to normalization. You can implement combo boxes on the form though. Its super easy, and does not violate normalization. There is a useful wizard and everything. In fact, it is the only wizard that I ever rely on, except very occasionally the query wizard for finding matches.

 

[exceii]

Most on this forum will tell you not to use lookups. They are counter to normalization. You can implement combo boxes on the form though. Its super easy, and does not violate normalization. There is a useful wizard and everything. In fact, it is the only wizard that I ever rely on, except very occasionally the query wizard for finding matches.

I've also heard that lookups should be avoided. Non-the-less I would still like to understand them.

 

[Alansidman]

Adding to the Speakers86 comment, look at this:

http://access.mvps.org/access/lookupfields.htm

Alan

 

[boblarson]

I've also heard that lookups should be avoided. Non-the-less I would still like to understand them.

You don't avoid lookups because they are useful. The thing you avoid is using them directly in table fields. You still use them from FORMS using combo boxes and listboxes. You just don't put them directly in tables using the lookup tab. And Microsoft has a habit of putting in stuff that isn't "best practices" in their samples because they want to show off their things they have put in place that can be used. But just because they can be used doesn't mean that they should.

A quick explanation of lookups would be

1. It allows a way to store a normalized datum.
2. It then allows you to SEE this in a more user-friendly way.

So storing the ID number in the table but being able to show the user for them to see, and select, the "translation" of the ID into the text description.

Also read this for more about why using lookups at table level is not a good thing to do:
http://www.mvps.org/access/lookupfields.htm
(which is actually what Alan put in there - so it was a bit redundant of me to put it too )