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...
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.
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)
- Customer_ID - Primary Key
- Cust_Company - Name of customer's business (Text)
- Cust_Phone - Customer's phone number (Text)
- Cust_Company - Look-up to tbl_Customer-Cust_Company
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.
