converting old table to new structure - revised

  • Thread starter Thread starter sarfaraz
  • Start date Start date
S

sarfaraz

"sorry Im my earlier post the email ID has some mistake"

I have a legacy old database in access which has two fields
name and qualification. The name field appears more than
once for multiple qualification. say mr A has MBBS,MD,PHD
as his qualifaction then there are three records with the
same name for each qualification. I want a table with name
and qualiifacation1, qualification2 ... and so on sothan
each records will be indexed on name . and no two records
will have the same name repearing.
What is simplest method do this ? without writing any code
of VBA etc..
or is there any way to qet a query/report with the output
as:- ( for mr. A above)

Mr.A - MBBS,MD,PHD in sigle line

..
 
You can use this function. Paste it into a new module and save the module as
"basConcatenate". Then you can use it in a query as suggested in the
comments.

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
 
Thanks a lot for the reply , guidance the function code.I
will try it.
-----Original Message-----
You can use this function. Paste it into a new module and save the module as
"basConcatenate". Then you can use it in a query as suggested in the
comments.

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

--
Duane Hookom
MS Access MVP


sarfaraz said:
"sorry Im my earlier post the email ID has some mistake"

I have a legacy old database in access which has two fields
name and qualification. The name field appears more than
once for multiple qualification. say mr A has MBBS,MD,PHD
as his qualifaction then there are three records with the
same name for each qualification. I want a table with name
and qualiifacation1, qualification2 ... and so on sothan
each records will be indexed on name . and no two records
will have the same name repearing.
What is simplest method do this ? without writing any code
of VBA etc..
or is there any way to qet a query/report with the output
as:- ( for mr. A above)

Mr.A - MBBS,MD,PHD in sigle line

.
Click to expand...


.
Click to expand...
 
Back
Top