MS Access Moving records into fields

Question

I have an ODBC connection to a database I don't own and can't change. What I am looking to do is to make related records merge into one record. The relationship is a 1 to many.

I have a student managment system and want to export a call out list which feeds an automated callout service (charged by Call). I want to be able to call a house only once if there are multiple students living there.

Desired Call out file structure:

PHONE     Inst1                  Inst2                  Inst3
555-5555  John was absent today  Jane was absent today  Joe was absent today

as apposed to existing data:

PHONE     Inst
555-5555  John was absent today  
555-5555  Jane was absent today  
555-5555  Joe was absent today

Any suggestions?

Answer 1

My first inclination was to use a crosstab query; however, that could get a bit hairy.

What about cutting some VBA code in a module to concatenate them together and then insert in the other table (something like the following - completely untested and probably broken somewhere)?

dim strAbsent as string
dim currPhone as string
dim lastPhone as string
Dim db As Database
Dim rstAbsent As Recordset
Dim rstAbsentReport As Recordset

Set db = CurrentDb
Set rstAbsent = dbV.OpenRecordset("select * from Absent", _
                                    dbOpenDynaset, dbSeeChanges)
Set rstAbsentReport = dbV.OpenRecordset("select * from AbsentReport", _
                                        dbOpenDynaset, dbSeeChanges)

'...
do while not rstAbsentReport.EOF
    currPhone = rstAbsentReport("phone")
    lastPhone = currPhone 
    do while currPhone = lastPhone _
            and not rstAbsentReport.EOF
        strAbsent = strAbsent & ";" & rstAbsentReport ("comment")
        'Yes I know concatenating strings this way is terribly inefficient

        rstAbsentReport.MoveNext
        if not rstAbsentReport.EOF then
            currPhone = rstAbsentReport("phone")
        end if
    last
    rstAbsent.AddNew
    rstAbsent ("call") = strAbsent
    rstAbsent.Update
loop
'... clean up of recordset variables left as an exercise

Answer 2

Here is a thread that required a similar solution:

http://stackoverflow.com/questions/92698/combine-rows-in-access-2007

Answer 3

Another similar question is:

http://stackoverflow.com/questions/747021/flatten-relational-data-for-summary-export

althought the accepted solution there requires you to choose an upper limit to how many students will be included on a single line.