Unanswered: Create table with Data from several records
I have a table with these fields:
Since one product may be compatible with several different models, the product code is duplicated for every model. I need to create a new table so I can upload the list on my website without having the product codes duplicated.
The new format should be like this:
What I want to do is to create a new table from the old one, and for everytime a product is duplicated, it will add itself like this in one of the category fields: maincategoryname/subcategory/Manufacturer/Model
I am not an expert, I just started to work with access a few months ago. Please Help me.
I would suggest a different design. Have a product table and a models table. Create a third table, perhaps Product_Model_Compatibility which serves as a link between the two tables. Every time a new compatibility comes to light, you are simply adding a new record to the third table rather than changing the table structure. Changing your table design constantly is not a good idea.
I already have a product table. What I want to do is append those data in a new table without having the product codes duplicate. In the new table, I would have a few fields called Category1, Category2, Category3..... Every model would be a category on its own, instead of a new record.
I was thinking about doing it manually, but I have over 100,000 parts in the source table. I was thinking if I can use a code to look at the source table, then extract the part number from the first record, then append the data in the new table, and everytime the product code is duplicated, it would look at the model, and append it in one of the category fields in of the record with its respective product code like this: maincategoryname/subcategory/Manufacturer/Model.
The main category and the subcategory will be fields from the source table.
I really need to get this to work.
You are trying to use hierarchal database design. That was pretty much replaced with the relational database design in the late 70's and 80's. Access is a relational database. The table structure is the basis for all of your queries, forms and reports. I don't understand the relationship between your products and your models. What is the difference between productID, ProductCode, category, and model? Can you give me some sample data:
ProductID ProductCode Description Manufacturer Model Price
"Prod1","Code1","Widgets - Right Handed","Acme","WRH",19.99
"Prod2","Code2","Widgets - Left Handed","Acme","WLH",19.99
"Prod3","Code3","Widgets - Either Hand","Acme","WBH",19.99
It is important to understand the physical world and business practices before trying to represent them in a database.
These tables would allow you to link more models to products without the hassle of changing the table structure. The problem with changing a table's structure is then all queries, forms and reports have to be redesigned.
Just in case this is a one time occurance, you may want to look into using a Make Table query (rather than the select, delete, append, or update queries you may already be used to).