By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,615 Members | 1,961 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,615 IT Pros & Developers. It's quick & easy.

MS ACCESS concatenation - move different row into another column in the one row

P: 10
Hi!

I need help in MS ACCESS, I need to create a query that could move different rows into another column. More like concatenation but in a different column rather than in one column. Example:

OLD TABLE

Stud# School Major YrGrad
00001 ABC Science 1996
00001 ROC Math 2000
00002 SAR Biology 1998
00002 SAR Chemical 2002
00003 ABC ART 2000



NEW Table

Stud# School1 Major1 YrGrad1 School2 Major2 YrGrad2
00001 ABC Science 1996 ROC Math 2000
00002 SAR Biology 1998 SAR Chemical 2000
00003 ABC ART 2000

How do i do this in MS ACCESS?
Mar 21 '07 #1
Share this Question
Share on Google+
6 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hi!

I need help in MS ACCESS, I need to create a query that could move different rows into another column. More like concatenation but in a different column rather than in one column. Example:

OLD TABLE

Stud# School Major YrGrad
00001 ABC Science 1996
00001 ROC Math 2000
00002 SAR Biology 1998
00002 SAR Chemical 2002
00003 ABC ART 2000



NEW Table

Stud# School1 Major1 YrGrad1 School2 Major2 YrGrad2
00001 ABC Science 1996 ROC Math 2000
00002 SAR Biology 1998 SAR Chemical 2000
00003 ABC ART 2000

How do i do this in MS ACCESS?
Essentially you can't really do this as it violates how table are supposed to work. What do you want this for?

Mary
Mar 21 '07 #2

P: 10
Hi Mary,

I need this to avoid multiple rows with the same student#. i want to elimanate this rows and contain them into one row for each student. I tried using concatenate (using modules) and it works fine although all concatenated fields are in one column see sample below.

ex.
Std# School Degree YrGrad
00001 ABC, ROC Science, Math 1996, 2000
00002 ROC, ROC Biology, Chemical 1998, 2002
00003 ABC ART 2000

But, is there a way to move the other entry into a diffferent column using ms access query and modules (We're not gonna alter the table itself but just to create a view query?

Is there a code for selecting a datarow in a dataset like in .net in this case is there a recordrow in a recordset for MS access?

I'm new in using MS access so im not yet familiar with the codings used here, but in .net i know to do this we can create a dataset (I think this is the counterpart of recordset in ACCESS), in the dataset we can choose which row of the dataset to display (ex. dataset.datarow(i) --> "i" is the row number 0 as the top and so on.) Is there such code in MS Access modules?
Mar 22 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
You would need something like ...
Expand|Select|Wrap|Line Numbers
  1. SELECT Std#,       
  2. Left(School, InStr(School,",")-1) As School1,                    
  3. Mid(School, Len(School1)+2, 
  4. IIf(Not IsNull(InStr(School,","),InStr(School,",")-1,Len(School))) As School2,                    
  5. Mid(School, Len(School1)+Len(School2)+4, 
  6. IIf(Not IsNull(InStr(School,","),InStr(School,",")-1,Len(School))) As School3,                    
  7. Left(Degree, InStr(Degree,",")-1) As Degree1,                    
  8. Mid(Degree, Len(Degree1)+2, 
  9. IIf(Not IsNull(InStr(Degree,","),InStr(Degree,",")-1,Len(Degree))) As Degree2,                    
  10. Mid(Degree, Len(Degree1)+Len(Degree2)+4, 
  11. IIf(Not IsNull(InStr(Degree,","),InStr(Degree,",")-1,Len(Degree))) As Degree3,                    
  12. Left(YrGrad, InStr(YrGrad,",")-1) As YrGrad1,                    
  13.  Mid(YrGrad, Len(YrGrad1)+2, 
  14.  IIf(Not IsNull(InStr(YrGrad,","),InStr(YrGrad,",")-1,Len(YrGrad))) As YrGrad2,                    
  15.  Mid(YrGrad, Len(YrGrad1)+Len(YrGrad2)+4, 
  16.  IIf(Not IsNull(InStr(YrGrad,","),InStr(YrGrad,",")-1,Len(YrGrad))) As YrGrad3
  17. FROM TableName
  18.  
This should work for up to three columns in each catagory. Anything more complicated and you'll really need to look at VBA Code

Mary
Mar 22 '07 #4

P: 10
Hi Mary,

The data im using is about .5 million rows and its not advisable to use IIF since it would just make the query to take longer. Im trying to use the vba (modules) in the access.. and i think i could get the result only if i could select a certain row number (row 0, 1, 2, 3, and so on...) is there a way to select certain row number based on the query.

Sample:

Select * from table.

Could i select the 3rd row only?

I know that the first row is easy since we can make use of "Top 1" query, but is it possible to select the next row (2nd row only) or the third row and so on... I think i can make use of the Select query string for this and make it into a function instead.

Another way i can think of is using an array for the rows that have the same student number... is this possible?
Mar 22 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Mary,

The data im using is about .5 million rows and its not advisable to use IIF since it would just make the query to take longer. Im trying to use the vba (modules) in the access.. and i think i could get the result only if i could select a certain row number (row 0, 1, 2, 3, and so on...) is there a way to select certain row number based on the query.

Sample:

Select * from table.

Could i select the 3rd row only?

I know that the first row is easy since we can make use of "Top 1" query, but is it possible to select the next row (2nd row only) or the third row and so on... I think i can make use of the Select query string for this and make it into a function instead.

Another way i can think of is using an array for the rows that have the same student number... is this possible?
The only way to do this is to order the results in the manner you want them to be accessed. You can't rely on the order they appear in the table.

For example ...
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim rs As DAO.Recordset
  3. Dim strSQL As String
  4. Dim i As Integer
  5.  
  6.    Set db = CurrentDb
  7.    strSQL = "SELECT * FROM Employees ORDER BY DateJoined"
  8.    Set rs = db.OpenRecordset(strSQL)
  9.  
  10.    rs.MoveFirst
  11.    For i =1 to 3 ' will move to the third record
  12.       rs.MoveNext
  13.    Next
  14.    ...
  15.    ...
  16.  
Mary
Mar 22 '07 #6

P: 10
Hi Mary!

Thanks for the help! I've finally done it! hehehe!

Here's my code hope this help to others:
Expand|Select|Wrap|Line Numbers
  1. Function SepSchool(Num As String, DRow As Integer) As Variant
  2. Dim MyDB As Database
  3. Dim rst As Recordset
  4. Dim strOut As String
  5. Dim strSql As String
  6. Dim lngLen As Long
  7. Dim i As Integer
  8.  
  9. Set MyDB = CurrentDb()
  10.  
  11. 'Select All University Per Personnel
  12. strSql = "SELECT * FROM PERS_EDUC_INFO_T INNER JOIN UNIV_INFO_T ON PERS_EDUC_INFO_T.UNIVERSITY_NUMBER = UNIV_INFO_T.UNIVERSITY_NUMBER WHERE PERSONNEL_ID_NUM = '" & Num & "';"
  13. Set rst = MyDB.OpenRecordset(strSql)
  14.  
  15.     i = 1
  16.  
  17.     With rst
  18.  
  19.         Do While i <= DRow
  20.             If .EOF Then
  21.                 strOut = ""
  22.             ElseIf Len(!UNIVERSITY_NAME) > 0 Then
  23.                 strOut = !UNIVERSITY_NAME
  24.             Else
  25.                 strOut = ""
  26.             End If
  27.             i = i + 1
  28.             If .EOF = False Then
  29.                 .MoveNext
  30.             End If
  31.         Loop
  32.  
  33.     End With
  34.  
  35.     rst.Close
  36.  
  37.     lngLen = Len(strOut)
  38.     If lngLen > 0 Then
  39.         SepSchool = Left(strOut, lngLen)
  40.     Else
  41.         SepSchool = Null
  42.     End If
  43.  
  44. Set rst = Nothing
  45. Set MyDB = Nothing
  46. End Function
  47.  
Mar 26 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.