Table Look-Ups (1 Viewer)

[Blitzer]

Having spent several months of trying to learn how to use Access 2007 I felt like I had been kicked in the teeth when I read that the general opinion is that using look-ups at table level is frowned upon.
However, depite getting the reasons for not having these look-up at table level do make sense, there seems to be little clear information in just how to do this in the "right" or approved way.

I have just spent the past two days (not solid of course!) to understand how you would then create this look-up on forms but not in your tables. Access of course makes it very easy to create the look-ups the "wrong" way. I am hoping I have now undertood how the look-up now works at table level, because to me there is still a look-up of sorts in the table.

I'm hoping that I now understand this correctly but would appreciate if someone who knows what they are doing could verify what I say next, by an example...

Let's assume we have two tables, tbl_Products & tbl_Customers.


tbl_Products has the following fields...

• Product_ID - Primary key (Autonumber)
• Part_Num - Code number for product (Text)
• Part_Desc - Desrciption of product (Text)
• Part_Rev - Revision level of product (Text)

tbl_Customer has the following fields...

• Customer_ID - Primary Key
• Cust_Company - Name of customer's business (Text)
• Cust_Phone - Customer's phone number (Text)

Now, to link these using the built in Access function you would end up with another "look-up" field in the tbl_Products table...

• Cust_Company - Look-up to tbl_Customer-Cust_Company

This by the information on here would be the "wrong" way to do it as the look-up makes it difficult (amongst other issues) makes it harder from a design POV to understand how the relationships withing in the database work - correct?

What has not been clear from the posts on here is that you still need a field in the tbl_Products table, store the ID of the record within the tbl_Customer table. However, rather than being a direct look-up and storing the data as Access 2007 would create by it's own look-up function all this field will store is the ID number of the record that relates directly to our record in tbl_Products - correct?

i.e.

Access 2007 would create a field in our tbl_Products - Cust_Company that would be a combo-box and store the actual name of the related company, e.g. Microsoft.

Using the "manual" method of creating this link/relationship would still require us to have a field in tbl_Products - Cust_Company (same name for ease of reference), but this time the field is simply a number field that will be used to store the ID of the record in tbl_Customer, i.e. Microsoft would be store as say 39 which would be the ID of the record of Microsoft in the tbl_Customer - Correct?

I hope this makes sense, it's quite straight forward in my head, not so easy to explain what I mean so other people can understand how it should be done.

Anyway, enogh of me rambling, if someone could please confirm that I have undertstood this correctly or not it would be much appreciated. BTW, I purposely did not go into creating the "look-up" at form level - this post was hard enough as it was LOL.

 

[jzwp22]

I'm not really sure what you are trying to say, but if you are tryng to associate certain products with a customer you need to bring them together in another table. You would only store the key values in that table (without lookups)

tblCustomerProducts
-pkCustProdID primary key, autonumber
-fkCustomerID foreign key to tblCustomers
-fkProductID foreign key to tblProducts


In order to enter data, you would use a form based on your customer table as your main form. You would use a subform (within the main form) based on the tblCustomerProducts table. You would use a combo box (a lookup) control on that subform to populate the fkProductID field. The combo box's row source would be based on tblProducts.

I've attached an example database.

 

[David Eagar]

If I may add a 'plain English' explanation, having spent some time unlearning my bad habits and getting to grips with the 'correct' way.

Create your tables, ensuring the field you wish to use as a lookup is set as an integer (make sure lookup table has an id field)

Create a query using your main table. Using properties of the lookup field, select combo box, select the lookup table (or query), bound column=1, coulmn count=2 (or more if needed), column widths 0,3 - this hides the autonumber and displays the text value

Always create forms from queries, so the lookup will automatically use the combo box you have created in the query

Hope this is clear

 

[SOS]

I found this too, if it helps...

 

[gemma-the-husky]

i spent ages answering this, and lost it.

Here's a practical explanation.

as pointed out, customers and products is not a suitable relationship for consideration, since it isnt a candidate for a table lookup. If you have this as a possibility there is something amiss with your structure

but think about customers and orders

each order has a customer. now assuming the customer has an id other than the name (typically maybe a short textstring, or an autonumber) then in the order file you need to store this id in order to relate the two tables.

NOW, this COULD be structured in the orders table as a lookup - so instead of seeing the id when you look at the table, you see the referenced customer name instead.

when you create a form, access automatically builds a combo box instead of just showing the id (i think)

NOW this becomes counterproductive when you want to investigate the date in your table - you want to see what is ACTUALLY stored in the order table, but instead you see the dereferenced name - and this is really irritating at times

also what haPPens if you need to search or sort the data - does it use the ID or the dereferenced name - which might be different - it starts to get very confusing

SO generally, we don't use table lookups (nobody should SEE tables directly anyway) and if we need a comob box, or a list box, we add it ourselves.

MS added the lookup to try to be helpful, but it is best avoided.

 

[Blitzer]

I'm not really sure what you are trying to say, but if you are tryng to associate certain products with a customer you need to bring them together in another table. You would only store the key values in that table (without lookups)

tblCustomerProducts
-pkCustProdID primary key, autonumber
-fkCustomerID foreign key to tblCustomers
-fkProductID foreign key to tblProducts

The purpose of my message was to determine how avoid creating look-ups at table level rather than dealing with the actual Customer to Product relationships. That is to say - avoid the look-ups that Access 2007 would create for you, the ones where rather than storing a reference to a record in another table, what it actually does is store a value of afield from another table.
Surely your example would apply for a many-to-many relationships between many customers to many products and also many products?
What if I only want a one-to-many relationship, i.e. one customer is associated with many products, but each product is only associated with one customer?
In your example I can't see how this on-to-many relationship could/should work?

I have attached my own example DB, but to be honest after what you have said I'm not sure if the table structure is as it should be. My point being that I do still effectively have what I would regard as a look-up field in my products table.
However, rather than storing the data contained within a record of the customer table, what is actually stores is the ID of the record that has the data we are interested in.
To me this is once of the associated no-nos of look-ups at table level, but as I said I'm still not sure if this is how it should be done or whether al I have done is create another kind of look-up?

I hope I have given a clearer reason for my original post now.
Please note that if you look at my example DB, that it's not the purpose of the tables/forms that is important. What I'm trying to find out is how to avoid having look-ups at table level - I don't think I have it right to be honest. I don't mind being wrong I just need to understand how it should be done my practical examples preferably.

Thanks again.

 

[Blitzer]

Sorry - try again with the attachment. This is my first time trying to post one - please be patient!

 

[jzwp22]

I will have to take a look at your database this evening since I do not have Access 2007 here at work. In the table design, you can designate field look up as text boxes, list boxes or combo boxes. To avoid lookups, the field should be set up as text boxes. You will still store the value of the related primary key in the foreign key field.

 

[jzwp22]

Your tables look fine as does your relationship between the two tables. For the form, you would put the product for as a subform within the customer form. I've done that in the attached database.

 

[Blitzer]

Your tables look fine as does your relationship between the two tables. For the form, you would put the product for as a subform within the customer form. I've done that in the attached database.

Thanks very much for your help. I had actually already used sub-forms in such a way and have actually taken a big step back to the basics after having read the various comments about look-ups at table level.

So, just to re-cap.
Although I have a field in my tbl_Products that is a effectively a "look-up" of sorts to the tbl_Customers, the way I have done it is the accepted "right" way as it were?
That is to say that the Cust_Company field in the tbl_Products table only references a specfic record within the tbl_Customer table?
Or is my table structure still flawed in so much as it has a kind of look up in it at all?

Am I therefore correct in saying that, had I used Access to do this for me, that it would have effectively replicated the data already held in the field Cust_Customer in the table tbl_Customer and then stored this data again in the Customer_Customer field of the tbl_Products?
For this reason the built-in look-up function that Access provides is therefore frowned-upon?

Sorry to go on about this, I just need to know I have got it right before I go back to creating databases.

 

[SOS]

Sorry to go on about this, I just need to know I have got it right before I go back to creating databases.

So I didn't see this mentioned in this thread. Have you read it?

 

[jzwp22]

That is to say that the Cust_Company field in the tbl_Products table only references a specfic record within the tbl_Customer table?
Or is my table structure still flawed in so much as it has a kind of look up in it at all?

The way you have your tables set up is fine now.

...effectively replicated the data already held in the field Cust_Customer in the table tbl_Customer and then stored this data again in the Customer_Customer field of the tbl_Products?

You are correct but it is only one of the problems of lookups as indicated in the link that SOS provided in his post.

 

[Blitzer]

So I didn't see this mentioned in this thread. Have you read it?

Yes, thanks for the link. I have read that a few times over, but to be perfectly honest, most of it is going over my head.

 

[jzwp22]

I would not get hung up on it at this point. I think as you get more familiar with Access, the discussion in the link provided by SOS will become clearer. I would consider it a general tip to keep in mind as you build your database just like not using spaces or special characters in your table or fieldnames and not using reserved words as field/table names (see this link )