Not sure how to title this... Issue with Table structure in MS Access

[jdhamot]

Hi Everyone,

I am currently dealing with a table in Microsoft Access 2003 . This is for a data conversion where my current database has a repeating Style for every barcode associated with it. The new system's table structure wants one row for each Style and the barcodes filter through multiple columns in the same row. Any help would be greatly appreciated!!

Below is a very simple example:

I have a table that looks like this:

Style Barcode
Shoe 100
Shoe 200
Shirt 300
Pant 400
Pant 500
Pant 600
Sweater 700

I need it to look like this:

Style Barcode1 Barcode2 Barcode3
Shoe 100 200
Shirt 300
Pant 400 500 600
Sweater 700

I know that as soon as someone answers this it will all click, and come back to me. In the meanwhile I seem to be stuck and very frustrated. I greatly appreciate any input whether it's through code in a module or a sql query.

Thank you!
Josh

 

[Remou]

This looks like a crosstab query.

 

[jdhamot]

Thanks for the reply Remou. I did look into crosstab queries with no luck. I thought that since I'm really not totaling anything that was the reason for the strange data resulting from the crosstab query.

 

[Remou]

I see what you mean. Perhaps it is time for code? It may be possible to use DHookoms function (faq701-4233) to output a CSV.

 

[jdhamot]

Remou,

I think I'm following you here. My problem is that while I loop through the table I can't get the multiple barcodes in separate columns for the same style. You are suggesting that I append to the same column and separate the barcodes with a comma. Instead of having 9 columns per row I would just have 2 columns per row with one of those columns having every barcode in it separated by a comma. So when I export to a csv file it should put those into their own columns.

I'm gonna give that a try, unless I am completely off base with your suggestion, I think it might work. The only problem is if a with...end with statement will work that way when using update.

Thanks,
Josh

 

[dhookom]

If you really need separate columns, you can use a crosstab with SQL like:

TRANSFORM First(tbljdhamot.Barcode) AS FirstOfBarcode
SELECT tbljdhamot.Style
FROM tbljdhamot
GROUP BY tbljdhamot.Style
PIVOT "Barcode" & DCount("*","tbljdhamot","Style = """ & [Style] & """ AND Barcode<=""" & [Barcode] & """");

This may run quite slow if you have lots of records.

BTW: you should provide actual field and table names when asking questions here so we don't have to make them up ;-)

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[jdhamot]

I would like to thank everyone who took the time to help with this issue. To see how I was able to get the solution I was looking for with the help of Tek-Tips please reference this thread:

http://www.tek-tips.com/viewthread.cfm?qid=1443457&page=1

Thank you again!
Josh