• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

Access - Transpose Data Rows

In the following example, I will be adding the first three columns to an already existing table:

RecordID Line No  PersonID    Field1            Field2             Field3              Field4

1              1             1             John Smith      2009              Pass                 12/2008
2              2              1            Biochemisty      MD                Rockvillle           20852
3              3              1                                  Honors

There will be multiple individuals with this format. Not all individuals will have all Line No 1-3 (i.e. a person can have 1, 2, or 3, lines).

The end result will be one table, where each individual (Columns 1 - 3 all equal 1), will transpose the bottom one or two rows (Line No 2 and 3), into Line No 1.

I have not been successful in my initial attempts, in A. auto numbering the first three columns depending on how many rows the individial has (between 1 and 3), and then transposing all rows to one row for the individual.

Assistance with SQL coding is appreciated. Thank you
0
tahirih
Asked:
tahirih
  • 6
  • 4
  • 2
2 Solutions
 
dqmqCommented:
Considering only the columns that already exist, how do you know which lines 2 and 3 go with which line 1?   In your sample, no relationship between John Smith and Biochemistry is apparent.  
0
 
dqmqCommented:
As I'm waiting for an answer, I anticipate you will tell me the rows are in order, i.e. John Smiths rows are grouped together followed by the next person.   If that's the case, and that's all we have, you are out of luck, my friend.  

Waiting anxiously...
0
 
tahirihAuthor Commented:
Yes, this is what we have. All I can offer is that each individual has between 1 and 3 rows, in consequential manner (i.e. All of John Smith's rows, then all of Jane's, etc.)

Can you think of a way to combine up to three rows into one per person. I have not been able to program this in SQL.

Thanks.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rey Obrero (Capricorn1)Commented:
post the expected outcome
0
 
tahirihAuthor Commented:
This is what I would want to see, per individual (we have highlighted only one individual:

John Smith  2009  Pass   12/2008  Biochemisty MD Rockvillle 20852  Honors

Please note that from "Biochemistry" to "Honors, we would be creating new fields (Fields 5 - 9)

Thanks
0
 
Rey Obrero (Capricorn1)Commented:
what would be the names of the additional fields?
you will need a User define function to do this

upload a db with the table. check attach file below
0
 
dqmqCommented:
>Can you think of a way to combine up to three rows into one per person. I have not been able to program this in SQL.


If you populate LineNo and PersonID manually, then it goes like this:

Select
 O1.PersonID as PersonID
,O1.Field1 as Name
,O1.Field2 as Year
,O1.Field3 as Grade
,O1.Field4 as Whatever
,O2.Field1 as Major
,O2.Field2 as Degree
...
,O3.Field4 as ?????
  into NEWTABLE
FROM
OLDTABLE O1
left join OLDTABLE O2 on O1.personID = O2.PersonID and O1.LineNo = 1 and O2.LineNo = 2
left join OLDTABLE O3 on O3.personID = O1.PersonID and O1.LineNo = 1 and O3.LineNo = 3

0
 
tahirihAuthor Commented:
Please note that I will not be manually populating the preceeding three ID columns. This would be automated  - and the catch here is not all individuals will have all three rows.

0
 
dqmqCommented:
>Please note that I will not be manually populating the preceeding three ID columns. This would be automated  - and the catch here is not all individuals will have all three rows.

I don't care how they get populated.  Once populated, then my SQL combines 1 to 3 rows into a single row.  The only requirement are that every Person has LineNo=1.

Unfortunately, there is no way to populate the first 3 columns automatically with the data you have supplied.  Good Luck.



0
 
dqmqCommented:
Oh...since this is Access you may need parens:

Select
 O1.PersonID as PersonID
,O1.Field1 as Name
,O1.Field2 as Year
,O1.Field3 as Grade
,O1.Field4 as Whatever
,O2.Field1 as Major
,O2.Field2 as Degree
...
,O3.Field4 as ?????
  into NEWTABLE
FROM
((OLDTABLE O1
left join OLDTABLE O2 on O1.personID = O2.PersonID and O1.LineNo = 1 and O2.LineNo = 2)
left join OLDTABLE O3 on O3.personID = O1.PersonID and O1.LineNo = 1 and O3.LineNo = 3)
0
 
tahirihAuthor Commented:
Please find attached an example table.

Also, note that I have purposely indicated that Jane S. does not have a third row. In the "New" table, the first three identifying columns are removed.

Hope this helps.

Thanks
Sample.xls
0
 
dqmqCommented:
I strongly recommend you retain PersonID in your final table.  Aside from that, this is my third posting of essentially the same solution. You seem to be hugely concerned about the absent rows, which are taken care of by the LEFT join.  You don't have to trust me...try the solution and get back with any issues. :>)

Select
 O1.PersonID as PersonID
,O1.ClassMate
,O1.Year
,O1.PassFail
,O1.GraduationDate
,O2.ClassMate as Major
,O2.Year as State
,O2.PassFail as City
,O2.GraduationDate as ZipCode
,O3.ClassMate as Merits
INTO  NEWTABLE
FROM
((OLDTABLE O1
left join OLDTABLE O2 on O1.personID = O2.PersonID and O1.LineNo = 1 and O2.LineNo = 2)
left join OLDTABLE O3 on O3.personID = O1.PersonID and O1.LineNo = 1 and O3.LineNo = 3)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now