Merging 2 tables

todavy

Registered User.
Local time
Today, 13:19
Joined
Dec 1, 2009
Messages
17
I have an Access 2007 table with two fields, ProductName and ProductSort:


ProductName / ProductSort
juice / cool
juice / tropical
juice / natural
cola / cool
cola / artificial

And I want it to become like this:

ProductSum
juice, cool, tropical, natural
cola, cool, artificial
..etc

In other words, the ProductName field should be merged with the ProductSort field to a new textfield called ProductSum with ProductName and all related ProductSorts separated with commas (one uniqe product per row).

Is this possible by using a Query? If not how?
 
I see very little scope of achieving this using queries. I think VB interference is necessary. I'm not versed with VB knowledge. However, the logic will be to traverse each record (for loop) in a recordset of one Product, appending the ProductSort field to a string and finally displaying it.

Am eager to see responses from those who know VB, as to how this could be done....
 
Another way?

I was afraid of going into the VB code. But isn't it possible to solve this way?

From this table:

ProductName / ProductSort
juice / cool
juice / tropical
juice / natural
cola / cool
cola / artificial

To a new table:

ProductName / ProductSort1 / ProductSort2 / ProductSort3 / [etc...]
juice / cool / tropical / natural
cola / cool / artificial

And then concat the fields with a "&" sign to a new table. Or am I out in the ozon here...
 
Just an observation. Perhaps you would benefit from reading up on Data Normalization
 
Data normalization?

Thanks for the observation, but I don't really think that is the question here. The problem is that I have "productcategories" on the "many"-side of a one-to-many relationship (actually there's a junction table between the products- and categories table that makes it possible for one product to belong to many categories and vice versa).

The trick is that, from the constallation above, make a .csv file (textfile) with this design:

category1@category@category2 [etc..], productId, price, weight [etc..]

Where the "@" (at) sign separates the productcategories and the "," (comma) sign separates the other product info.

I can't change the design of the .csv file. I'm using that to upload product to a webshop (Wosbee.com) and it has to be in that format.
 
You can do it in 2 steps.

First write an insert query that inserts distinct ProductNames, with Null ProductSorts from your Product table to the NewTable.
Next write an update query that appends ProductSorts from Product table to NewTable where NewTable.ProductName=ProductTable.ProductName
 

Users who are viewing this thread

Back
Top Bottom