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

[NarutoFanatic]

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?

[MMcCarthy]

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

[NarutoFanatic]

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?

[MMcCarthy]

You would need something like ...

SELECT Std#,       
Left(School, InStr(School,",")-1) As School1,                    
Mid(School, Len(School1)+2, 
IIf(Not IsNull(InStr(School,","),InStr(School,",")-1,Len(School))) As School2,                    
Mid(School, Len(School1)+Len(School2)+4, 
IIf(Not IsNull(InStr(School,","),InStr(School,",")-1,Len(School))) As School3,                    
Left(Degree, InStr(Degree,",")-1) As Degree1,                    
Mid(Degree, Len(Degree1)+2, 
IIf(Not IsNull(InStr(Degree,","),InStr(Degree,",")-1,Len(Degree))) As Degree2,                    
Mid(Degree, Len(Degree1)+Len(Degree2)+4, 
IIf(Not IsNull(InStr(Degree,","),InStr(Degree,",")-1,Len(Degree))) As Degree3,                    
Left(YrGrad, InStr(YrGrad,",")-1) As YrGrad1,                    
 Mid(YrGrad, Len(YrGrad1)+2, 
 IIf(Not IsNull(InStr(YrGrad,","),InStr(YrGrad,",")-1,Len(YrGrad))) As YrGrad2,                    
 Mid(YrGrad, Len(YrGrad1)+Len(YrGrad2)+4, 
 IIf(Not IsNull(InStr(YrGrad,","),InStr(YrGrad,",")-1,Len(YrGrad))) As YrGrad3
FROM TableName
 

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

[NarutoFanatic]

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?

[MMcCarthy]

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

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim i As Integer
 
   Set db = CurrentDb
   strSQL = "SELECT * FROM Employees ORDER BY DateJoined"
   Set rs = db.OpenRecordset(strSQL)
 
   rs.MoveFirst
   For i =1 to 3 ' will move to the third record
      rs.MoveNext
   Next
   ...
   ...
 

Mary

[NarutoFanatic]

Hi Mary!

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

Here's my code hope this help to others:

Function SepSchool(Num As String, DRow As Integer) As Variant
Dim MyDB As Database
Dim rst As Recordset
Dim strOut As String
Dim strSql As String
Dim lngLen As Long
Dim i As Integer
 
Set MyDB = CurrentDb()
 
'Select All University Per Personnel
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 & "';"
Set rst = MyDB.OpenRecordset(strSql)
 
    i = 1
 
    With rst
 
        Do While i <= DRow
            If .EOF Then
                strOut = ""
            ElseIf Len(!UNIVERSITY_NAME) > 0 Then
                strOut = !UNIVERSITY_NAME
            Else
                strOut = ""
            End If
            i = i + 1
            If .EOF = False Then
                .MoveNext
            End If
        Loop
 
    End With
 
    rst.Close
 
    lngLen = Len(strOut)
    If lngLen > 0 Then
        SepSchool = Left(strOut, lngLen)
    Else
        SepSchool = Null
    End If
 
Set rst = Nothing
Set MyDB = Nothing
End Function