Hi Alex,
This should do it, I think ...
Design a form based on the table that has the repeated data, and create a
command button on it named "cmdMergeMemo"
Copy and paste this code into it's "On Click" event procedure.
Notice that the form's navigation bar displays your record count = 7 before
you press the button, but only shows 2 records (with the complete memos)
after.
************************************************
Private Sub cmdMergeMemo_Click()
On Error Resume Next
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
Dim strUnique As String
Dim strCompare
Dim strMemo As String
Dim i As Integer
With rst
.MoveLast
.MoveFirst
For i = 0 To .RecordCount - 1
'Get the values for the current record
strUnique = ""
strUnique = Nz(!DateTime, "Null") & Nz(!AcctNumber, "Null")
'1.) Store the contents of the memo field in the "strMemo" variable.
If Len(strMemo) > 0 Then
strMemo = strMemo & " " & Trim(!Notes)
Else
strMemo = Trim(!Notes)
End If
.MoveNext 'Move to the next record (momentarily) to test unique
values
strCompare = ""
strCompare = Nz(!DateTime, 0) & Nz(!AcctNumber, 0)
'---------------------------------------------------------------------
If strCompare = strUnique Then 'If the unique values ARE the same...
'2.) Delete the record 'cuz we know its repeated in the next record.
'---------------------------------------------------------------------
.MovePrevious
.Delete
'---------------------------------------------------------------------
Else 'If they're NOT the same....
'---------------------------------------------------------------------
'3.)Now that we are in the last record conaining the unique
value...
' Write the contents of the variable to the memo field.
.MovePrevious
.Edit
!Notes = strMemo
.Update
strMemo = ""
End If
.MoveNext
Next i
End With
Set rst = Nothing
strCompare = ""
End Sub
************************************************
--
HTH,
Don
=============================
Use
My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)
I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.
Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop
================================
"Alex" <al**@totallynerd.com> wrote in message
news:Mu********************@sysmatrix.net...
Hi all,
We're importing data from a propriatery database, and below is a snippet
of several lines:
DateTime AcctNumber PtName Notes
12-23-2003 00432234 Smith, John Patient arrived from
12-23-2003 00432234 Smith, John ER with broken leg
12-23-2003 00432234 Smith, John and was admitted to
12-23-2003 00432234 Smith, John room 204.
12-24-2003 00432344 Thompson, Mike Patient sent by
12-24-2003 00432344 Thompson, Mike Primary Care physician
12-24-2003 00432344 Thompson, Mike and is room 205
(this is dummy data of course)
What I need is to group by DateTime, AcctNumber, and PtName and combine
Notes into one field, like this:
DateTime AcctNumber PtName Notes
12-23-2003 00432234 Smith, John Patient arrived from ER with broken leg
and was admitted to room 204.
12-24-2003 00432344 Thompson, Mike Patient sent by Primary Care
physician and is room 205
So in this example instead of 7 rows I'd only have two rows. I've played
with looping and several other methods, but nothing works. Can someone make a
suggestion?
Thanks. Oh, and this is on MS Access 2000 with all updates running on MS
Windows 2000 Pro.
Alex.
