Combine Tables with a Twist

[robertphtx]

I am trying to figure out how to do a task in Access and hopeful someone here can help me out.

What I have is three tables that all relate to wholesalers inventory and products.

One is my main table that has product item number (1st column), descriptions, etc. This table has 15.5k items/ product numbers.

Second table has attributes for some of the products that are in the main file. It also has in the first column the item number of the product. Note: not all products in main file have attributes. This table has about 13k items/ product numbers.

Third table has item number column then a column with item numbers that are cross sell items. It also has a category/ department no. column (third column) for the second column item numbers. This table has about 12k item numbers.

What I want to do is combine all three of these files into one table. Of course item numbers (first column) from the second and third table should match to the appropriate item numbers in the main table but not repeat item number.

If anyone understands this could you please help.

Thank you - Robert

[meratigoerr]

If I understand you correctly then your third table has two (or more) foriegn keys. If your goal is simply make one table out of the three, then table two and table three with the first foreign key are a simple matter. What ever process you use to add the records of tables 2 and 3 to your current table (Update query?) you will just repeat seperately again for table 3. This time however just change your relationship from the first forign key to the second (or third, or fourth)

The second table is pretty cut and dry. If your primary key is also your foriegn key, then you can easily combine it with your main table. Two seperate tables are probably not necessary. On your third table, because 1 record could relate to multiple records of your main table, you may want to consider leaving it as is. It may be more efficient to add a foriegn key to your main table to refer to your thrid table.

[Charlie_B]

Just to give a little more detail to the answer above:

To combine the first two tables, go into design view for table1 and add any fields that only exist in table2. Then, create an update query to move the data from the second table into the first. Assuming there's only one extra field in the second table, called 'attribute', it'd look something like this:

UPDATE [table1] INNER JOIN [table2] ON [table1].[item_number] = [table2].[item_number]
SET [table2].[attribute] = [table1].[attribute];

I'm afraid I don't quite understand the third table - what's a cross sell item? As meratigoerr says, if one item number can have more than one cross sell item (ie the same item number appears more than once in the table) it's not a good idea to combine it with the first table. If each item can only have a single cross-sell item then you can follow the same procedure as given above.