Building A String From Multiple Records (1 Viewer)

[Moonshine]

Hi All

Ive been trying to do this for the past hour, and failing each time i try! Time to try and pick your brains

I have a table, called "Clients". This is a list of around 6000 records, with 2 Fields in the table. Field 1 is "PARIS ID" and Field 2 is "SERVICE". Looking Like:

1002154 EA
1002154 Meals
1006454 EA
1065455 HOMECARE

And so on, a client could have just ONE service, thus appearing in the table once, or the client could have 4 Services, appearing in the table 4 times. What i want to do is create 1 record per Client, and build the string of their services, For Example:

1002154 EA, Meals
1006454 EA
1065455 HOMECARE

Is this possible? I cant get it to work for the life of me!

 

[bat1799]

Create another table called ClientsText with the same structure as Clients and see if this code does what you need.

Function MakeList()
Dim rst As DAO.Recordset
Dim rstSave As DAO.Recordset
Dim strSql As String
Dim tmpID As Long
Dim tmpStr As String
strSql = "SELECT Clients.* FROM Clients ORDER BY Clients.[Paris ID];"
Set rst = CurrentDb.OpenRecordset(strSql)
Set rstSave = CurrentDb.OpenRecordset("ClientsText")

rst.MoveFirst
tmpStr = rst![SERVICE]
tmpID = rst![Paris ID]
rst.MoveNext
While Not rst.EOF
    If rst![Paris ID] = tmpID Then
        ' same record so add to string
        tmpStr = tmpStr & ", " & rst![SERVICE]
    Else
        ' save record and reset temps
        rstSave.AddNew
        rstSave![Paris ID] = tmpID
        rstSave![SERVICE] = tmpStr
        rstSave.update
        tmpID = rst![Paris ID]
        tmpStr = rst![SERVICE]
    End If
   rst.MoveNext
Wend
' save last record
rstSave.AddNew
rstSave![Paris ID] = tmpID
rstSave![SERVICE] = tmpStr
rstSave.update
End Function

HTH

Peter

 

[Moonshine]

WOW!

it worked like a charm, thank you so much Peter!!

Scott

 

[skea]

good work there peter.
Thats another tool for me to play around with.

 

[skea]

So, If one has more than 5 fields in a table, it implies that he has to reference all those fields in one module.

 

[Moonshine]

That would make sense, with a little bit of editing i presume you could make fields along the row into one.... Instead of like what im doing, and making values in the same column.

 

[skea]

So, If one has more than 5 fields in a table, it implies that he has to reference all those fields in one module.

I think one can, I will try and put under thy nose.

 

[skea]

Ok. Peter, ive been playing around with you code but seems i cant get along unless i assign all fields in the old Table to the Function
This is what iam trying to do

Function MakeList()
Dim rsOld As DAO.Recordset
Dim rsNew As DAO.Recordset
Dim strSql As String
dim tmpInfrustructureID As Long, tmpORG_ABBR As String
Dim i As Integer
Set rsNew = CurrentDb.OpenRecordset("SELECT * FROM RunTable")
strSql = "SELECT Query1.* FROM Query1"
Set rsOld = CurrentDb.OpenRecordset(strSql)

rsOld.MoveFirst
tmpInfrustructureID = rsOld![InfrustructureID]
tmpORG_ABBR = rsOld![ORG_ABBR]
rsOld.MoveNext
Do Until rsOld.EOF
    If rsOld![InfrustructureID] = tmpInfrustructureID Then
        ' same record so add to string
        tmpORG_ABBR = tmpORG_ABBR & ", " & rsOld![ORG_ABBR]
    Else
        ' save record and reset temps
        rsNew.AddNew
         For i = 0 To rsOld.Fields.Count - 1
            If i = 2 Or i = 9 Then
             rsNew![InfrustructureID] = tmpInfrustructureID
            rsNew![ORG_ABBR] = tmpORG_ABBR
         Else
             rsNew.Fields(i).Value = rsOld.Fields(i).Value
        End If
        Next
        rsNew.Update
        tmpInfrustructureID = rsOld![InfrustructureID]
       tmpORG_ABBR = rsOld![ORG_ABBR]
    End If
      rsOld.MoveNext
Loop
' save last record
rsNew.AddNew
rsNew![InfrustructureID] = tmpInfrustructureID
rsNew![ORG_ABBR] = tmpORG_ABBR
rsNew.Update

End Function

It gives me the results but not according to the order of "tmpInfrustructureID".
Any Idea of where iam gettin it wrong!!

 

[bat1799]

It looks to me like you need to normalise your data.
Split the old data table in two with one table having InfrustructureID and ORG_ABBR
the other with everything but ORG_ABBR.
But can join them back together in a query to get what you have now or run the original code to 'string' ORG_ABBR.
Keeping ORG_ABBR seperate will give you more flexibility so you only want to 'string' them for reports.

Hope that makes sense!

Peter

 

[skea]

Thanks,It helped. Before i was appending data from another query and not a table. So now, i append from a table to another then run the query.
Great for my detailed reports.

 

[bat1799]

Glad you have it sorted

 

[skea]

Once Again. This code is my hero. It saved my A** a great deal.
Thanks A Million Peter.