Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

jdhamot

MIS
Apr 7, 2004
43
US
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
 
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.
 
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.
 
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
 
If you really need separate columns, you can use a crosstab with SQL like:
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top