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!

*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.

Jobs

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!

Resources

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