×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

ACCESS 2010 SQL Columns to Rows

ACCESS 2010 SQL Columns to Rows

ACCESS 2010 SQL Columns to Rows

(OP)
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

RE: ACCESS 2010 SQL Columns to Rows

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.

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 

Duane
Hook'D on Access
MS Access MVP

RE: ACCESS 2010 SQL Columns to Rows

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

RE: ACCESS 2010 SQL Columns to Rows

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.

RE: ACCESS 2010 SQL Columns to Rows

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

RE: ACCESS 2010 SQL Columns to Rows

(OP)
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.

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 
 

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

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 
 

Does it make sense?

RE: ACCESS 2010 SQL Columns to Rows

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

CODE --> sql

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"); 

GrpNum	Item	Asset	Model
1	3	12347	1ABC456
2	2	12346	A1B2C3
3	1	12345	1234ABC
4	1	12345	1234ABC 

Duane
Hook'D on Access
MS Access MVP

RE: ACCESS 2010 SQL Columns to Rows

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close