ACCESS 2010 SQL Columns to Rows

[airwolf09]

Good Afternoon

I need some help transposing columns to rows based on ITEM number. I am using SQL but can't seem to figure it out.

My qry_Table

Field1 Field2
ITEM 1
ASSET 12345
MODEL 1234ABC
ITEM 2
ASSET 12346
MODEL A1B2C3
ITEM 3
ASSET 12347
MODEL 1ABC456

My desired results

ITEM ASSET MODEL
1 12345 1234ABC
2 12346 A1B2C3
3 12347 1ABC456

1) I need to make the items on Column Field1 the Row Headers
2) I need to make the items in Column Field2 the data based on the ITEM number.

I would appreciate any help.

Thank You

 

[dhookom]

Very hard to read. You seem to understand a little about using TGML. Please use the Pre tags so we can read your data.

I doubt this can be done in pure SQL without some value that ties records into groups of three. I would never assume the records are in the correct order. If you have a common group number then you could use a crosstab query.

[pre]GrpNum Field1 Field2
1 ITEM 1
1 ASSET 12345
1 MODEL 1234ABC
2 ITEM 2
2 ASSET 12346
2 MODEL A1B2C3
3 ITEM 3
3 ASSET 12347
3 MODEL 1ABC456[/pre]


Duane
Hook'D on Access
MS Access MVP

 

[airwolf09]

Yeah. I had a hard time trying to figure this one. How about using Visual Basic? Is there any hope there?

 

[Andrzejek]

Same problem no matter what you go with.
You need to have some way to connect/group/tie ITEM 2 with ASSET 12346 with MODEL A1B2C3

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dhookom]

Can you guarantee the order of the records? I have used a make table query with the records and then added an Autonumber afterward numbering records 1 - whatever. I then integer divide the autonumber by 3 to get the groups.

Duane
Hook'D on Access
MS Access MVP

 

[airwolf09]

Records would be guaranteed. There could be any amount of assets but if a column for grouping is required, the same amount of fields would be available.

[PRE]
GrpNum Field1 Field2
3 ITEM 1
3 ASSET 12345
3 MODEL 1234ABC
2 ITEM 2
2 ASSET 12346
2 MODEL A1B2C3
1 ITEM 3
1 ASSET 12347
1 MODEL 1ABC456
[/PRE]

So if I have 4 items, it would look like this

[PRE]
GrpNum Field1 Field2
4 ITEM 1
4 ASSET 12345
4 MODEL 1234ABC
3 ITEM 1
3 ASSET 12345
3 MODEL 1234ABC
2 ITEM 2
2 ASSET 12346
2 MODEL A1B2C3
1 ITEM 3
1 ASSET 12347
1 MODEL 1ABC456
[/PRE]

Does it make sense?

 

[dhookom]

You can create a crosstab query with GrpNum as the Row Heading, Field1 as the Column Heading, and First of Field2 as the value.

TRANSFORM First(qry_Table.Field2) AS FirstOfField2
SELECT qry_Table.GrpNum
FROM qry_Table
GROUP BY qry_Table.GrpNum
PIVOT qry_Table.Field1 In ("Item","Asset","Model");

[pre]GrpNum Item Asset Model
1 3 12347 1ABC456
2 2 12346 A1B2C3
3 1 12345 1234ABC
4 1 12345 1234ABC[/pre]

Duane
Hook'D on Access
MS Access MVP

 

[airwolf09]

Thank You for your reply. I see the relationship. Always a pleasure to have experts like you around.