Help combining records for people at same address

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

The database we use at work (and hence I can't change basic table structure)
is driving me mad. I have a list of patient appointments and want to create
a query that will create a field to allow me to text message everyone in the
family in one message (I have the texting bit sorted). At present I can do a
query and get 3 lines with Mum, Dad & Child but this results in 3 texts.

I want to realise they're all at the same address and send one text saying
"Hi John, Mary & Tommy you have an appopintment"

Tables are

Patient
Address
Telephone
Appointments

So it can pick all Patients with Apponintment on date and then pick out
their phone numbers. I just need it to twig that there are 3 at that address
and only send once (well, I can do this bit but then end up with no names
since have to leave these "unique" bits out to get single line....)

Help :(
 
David Mee said:
The database we use at work (and hence I can't change basic table structure)
is driving me mad. I have a list of patient appointments and want to create
a query that will create a field to allow me to text message everyone in the
family in one message (I have the texting bit sorted). At present I can do a
query and get 3 lines with Mum, Dad & Child but this results in 3 texts.

I want to realise they're all at the same address and send one text saying
"Hi John, Mary & Tommy you have an appopintment"

Tables are

Patient
Address
Telephone
Appointments

So it can pick all Patients with Apponintment on date and then pick out
their phone numbers. I just need it to twig that there are 3 at that address
and only send once (well, I can do this bit but then end up with no names
since have to leave these "unique" bits out to get single line....)
Click to expand...


Try using Duane's function in your query.

http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'
 
The code below modifies the original function to allow specification of the
last delimiter.
'====code begins here===========
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ", _
Optional pstrLastDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'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
'
'to get a return like Duane, Laura, Jake, and Chelsey
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID], ",",", and") 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
'length before last concatenation
Dim intLenB4Last As Integer
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
intLenB4Last = Len(strConcat)
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))
If pstrDelim <> pstrLastDelim Then
strConcat = Left(strConcat, _
intLenB4Last - Len(pstrDelim)) _
& pstrLastDelim & Mid(strConcat, intLenB4Last)
End If
End If
Concatenate = strConcat
End Function
'====code ends here===========
 
On Wed, 4 Jan 2006 05:29:02 -0800, David Mee <David
So it can pick all Patients with Apponintment on date and then pick out
their phone numbers. I just need it to twig that there are 3 at that address
and only send once (well, I can do this bit but then end up with no names
since have to leave these "unique" bits out to get single line....)

Help :(
Click to expand...

You'll need a little bit of VBA code to string together all the
patients from one address for that date. There's sample code at

http://www.mvps.org/access/modules/mdl0008.htm

If you need help post back.

John W. Vinson[MVP]
 
Back
Top