Combining fields in SQL statement -- not sure how to do this

[Alex]

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.

[Alex]

Re: Combining fields in SQL statement -- not sure how to do this

Alex wrote:
[color=blue]
> 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.[/color]

The page I sent didn't format properly (too many tabs I guess). Below is what I want the result table to look like

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

Thanks...

Alex.

[Don Leverton]

Re: Combining fields in SQL statement -- not sure how to do this

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 "cmdMergeMe mo"
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_Cl ick()
On Error Resume Next

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClo ne

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.Name@Telus.N et 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" <alex@totallyne rd.com> wrote in message
news:MuudndSoj7 5ViBXdRVn_iw@sy smatrix.net...[color=blue]
> Hi all,
>
> We're importing data from a propriatery database, and below is a snippet[/color]
of[color=blue]
> 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[/color]
Notes[color=blue]
> 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[/color]
with[color=blue]
> 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.[/color]

[Don Leverton]

Re: Combining fields in SQL statement -- not sure how to do this

Hi again,

Oops!
Add a "Me.Requery " just before that "End Sub" :-)

i.e. :
...
Set rst = Nothing
strCompare = ""
Me.Requery
End Sub

Don


"Don Leverton" <leveriteNoJunk Mail@telusplane t.net> wrote in message
news:x5Eic.4268 $en3.1404@edtnp s89...[color=blue]
> 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 "cmdMergeMe mo"
> Copy and paste this code into it's "On Click" event procedure.
>
> Notice that the form's navigation bar displays your record count = 7[/color]
before[color=blue]
> you press the button, but only shows 2 records (with the complete memos)
> after.
>
> *************** *************** *************** ***
> Private Sub cmdMergeMemo_Cl ick()
> On Error Resume Next
>
> Dim rst As DAO.Recordset
> Set rst = Me.RecordsetClo ne
>
> 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"[/color]
variable.[color=blue]
> If Len(strMemo) > 0 Then
> strMemo = strMemo & " " & Trim(!Notes)
> Else
> strMemo = Trim(!Notes)
> End If
>
> .MoveNext 'Move to the next record (momentarily) to test[/color]
unique[color=blue]
> values
> strCompare = ""
> strCompare = Nz(!DateTime, 0) & Nz(!AcctNumber, 0)
>
>
> '---------------------------------------------------------------------
>
> If strCompare = strUnique Then 'If the unique values ARE the[/color]
same...[color=blue]
> '2.) Delete the record 'cuz we know its repeated in the next[/color]
record.[color=blue]
>
> '---------------------------------------------------------------------
>
> .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.Name@Telus.N et 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" <alex@totallyne rd.com> wrote in message
> news:MuudndSoj7 5ViBXdRVn_iw@sy smatrix.net...[color=green]
> > Hi all,
> >
> > We're importing data from a propriatery database, and below is a snippet[/color]
> of[color=green]
> > 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[/color]
> Notes[color=green]
> > 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[/color][/color]
played[color=blue]
> with[color=green]
> > looping and several other methods, but nothing works. Can someone make[/color][/color]
a[color=blue][color=green]
> > suggestion?
> >
> > Thanks. Oh, and this is on MS Access 2000 with all updates running on[/color][/color]
MS[color=blue][color=green]
> > Windows 2000 Pro.
> >
> > Alex.[/color]
>
>[/color]