Moving row data into multiple columns

[Siiig]

Hey AccessForums,



Currently trying to come up with a way to break data that looks like this...

Item	Finish
100	Black
100	White
200	Red
300	Black
300	White
300	Red

And create a query to return this...

Item	Finish1	Finish2	Finish3
100	Black	White
200	Red
300	Black	White	Red

The main problem I seem to be facing is that I don't have a Key field or ID field of any sort. Running a "Count" against "Item" only returns exactly how many there are, and without a keyfield I can't seem to come up with any way to select the data based on the count of "Item". I have a VBA script that does something similar--in that it concatenates the "Finishes" into one column (i.e. Black/White/Red), but that's not suitable for my end result.

Anyone able to point me in the right direction? One of the biggest challenges with researching access related things like this is I have no idea what to search. Very frustrating because I would rather help myself than bug you guys if I can help it.

[June7]

Denormalizing data can be a challenge.

Review

http://forums.aspfree.com/microsoft-...ry-322123.html

http://www.datapigtechnologies.com/f.../crosstab.html

Another approach requires a unique record identifier. An autonumber type field will serve. Can easily add the field.

Query1
SELECT Table1.ID, Table1.Item, Table1.Finish, DCount("*","Table1","Item=" & [item] & " AND ID<" & [ID])+1 AS Seq FROM Table1;

Query2
TRANSFORM Max(Query1.Finish) AS MaxOfFinish
SELECT Query1.Item
FROM Query1
GROUP BY Query1.Item
PIVOT Query1.Seq;

[Siiig]

Query1
SELECT Table1.ID, Table1.Item, Table1.Finish, DCount("*","Table1","Item=" & [item] & " AND ID<" & [ID])+1 AS Seq FROM Table1;

Thanks very much for the reply June! I actually managed to find a set of steps to add an ID column to this table, but now I'm struggling with the DCount function.

My "Item" field actually winds up being a Text field, which throws a "Data type mismatch in criteria expression." error message =( Is there a way to modify the DCount to allow text--and still have it count properly?

[June7]

Text type fields require apostrophe delimiters for the parameter. Date/time type uses # character.

"Item='" & [Item] & "' AND


What do the values in the field look like?

[Siiig]

Text type fields require apostrophe delimiters for the parameter. Date/time type uses # character.

"Item='" & [Item] & "' AND


What do the values in the field look like?

Ah June! You are the best! I was putting the single quotes in the wrong place the whole time, and getting wildly random results, haha. I even tried using CStr at one point to no good result.

Just for anyone who might stumble upon this, my original data had no ID column. I managed to tackle that by creating a table with an autonumber field--but no data. Do a Make Table query joining this autonumber table with your original table (no joins, this will result in no records), then Append your original data back into this new table. Voila, sequential ID field.

[June7]

Why did you not simply add autonumber field to the table?

[Siiig]

In this case, the data I'm using is being imported via an ODBC connection to a ProvideX database--which I can't add an auto-numbering field to =/