Looks good, but of course I have questions! I will read up on normalization, thanks for the advice, but in the meantime I would like to know the steps you took and how it works. I see the table tblCustomers, and that makes sense that you have used a Customer ID as a primary key. How does this table tie back to the Main Table and also the form? What have you done to the form so that when I use the combo box it looks up from tblCustomers and updates tbltABLE? I'm not seeing the connection. Thanks for the help so far!
OK. There was one thing I had forgotten to do, so I attached an updated version to this post.
You are so welcome for the help.
The first thing I did was to create the customer table. With an autonumber as the ID and making that the primary key. The primary key is what you will use to relate the customer table to the main table.
The next thing I did was to add a primary key autonumber ID to the main table. The idea is to create a field that is unique to that specific record in the table.
I then changed your customer name field to a number, and renamed it CustomerID to match the primary key name in the customer table. However, in a table when you refer to a primary key from another table it is called a foreign key. This, of course erased what you had before, but as this is dummy data, it didn't matter so much.
The step I had forgotten, but which is now in this attachment, is to go into the relationships screen and build your relationship between the two tables. In this case Each record in your main table can only have one customer, AND each customer in your customers table can have many records in the main table. This is called a one-to-many relationship. In the relationship screen add your two tables. Then click and drag the customer ID from one table to the customer ID in the other table. A screen will pop up to create the relationship, here it is a good idea to click "enforce referential integrity", I am not quite sure what this does, but is is a good idea to do this nonetheless (I am pretty sure it helps maintain data integrity, just not sure how). Save the relationship layout.
Ok.... On your form, go into design view. right click on the square to the upper left of the form then click properties. Go to the data tab and place your cursor into the record source line. There should then be a box to the right of that line with three dots. click it. There will be a prompt that asks you if you want to build a query, say yes. Here, add all of your fields from the table to the QBE. click the "X" and when prompted to save click yes.
OK, now to add the customer part. Delete the control on the form that has the customer on it. In its place drag a combo box on the form and follow the instructions from that thread where I have already posted pictures of what to do. It's a wizard so it should be pretty easy.
Please let me know if you get stuck in this. It's a lot, I know, and I may not have explained it well.
Edit: Oh Yeah, the normalization bit is extremely important. Without knowing and understanding it, you will certainly not be using the database correctly, nor get the most out of what it can accompish for you. So I really do recommend reading up on it.
