CODEWITHSUNDEEP
ms-accessfieldrecorddenormalization
Himself
Himself

Reputation:

MS Access Moving records into fields

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?

Upvotes: 3

Views: 437

Answers (1)

BIBD
BIBD

Reputation: 15414

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

Upvotes: 1

Related Questions