Updating record with previous record based on criteria

[Guest]

An example of the table I am working with is below. The real table has 72 fields

The Problem

Looking for a way to update the “Note†field per “PatientID†with the previous note that does not say “See Otherâ€. Eliminating all “See Other†notes

The table has to be sorted by “PatientID†& “VisitDateâ€. Dates are all in (yymmdd) format

Hidden Problem being; Patient may have first visit with an actual note, 2nd - 4th visits note being "See Other", the 5th visit having a new actual note, and any following visits notes being "See Other". Visits 2 - 4 notes need to = visit 1 note, and visit 6 - 7 notes need to = visit 5 note

Table

“PatientIDâ€,â€Nameâ€,â€SSNâ€,â€VisitDateâ€,â€Noteâ€,â€NoteDateâ€
“08976543â€,â€Smith, Johnâ€,â€333224444â€,â€031122â€,â€Sent Letterâ€,â€031208â€
“08976543â€,â€Smith, Johnâ€,â€333224444â€,â€031219â€,â€See Otherâ€,â€031219â€
“08976543â€,â€Smith, Johnâ€,â€333224444â€,â€040103â€,â€See Otherâ€,â€040103â€
“08976543â€,â€Smith, Johnâ€,â€333224444â€,â€040109â€,â€See Otherâ€,â€040109â€
“08976543â€,â€Smith, Johnâ€,â€333224444â€,â€040205â€,â€Ordered Recordâ€,â€040401â€
“08976543â€,â€Smith, Johnâ€,â€333224444â€,â€040211â€,â€See Otherâ€,â€040211â€
“08976543â€,â€Smith, Johnâ€,â€333224444â€,â€040314â€,â€See Otherâ€,â€040314â€


Any help with this would be greatly appreciated
Thank

 

[John Nurick]

Simplest to do this by iterating through a recordset. Air code:

Dim dbD as DAO.Database
Dim rsR As DAO.Recordset
Dim strLastNote As String

Set dbD = CurrentDB()
Set rsR = dbD.OpenRecordSet("MyQuery")

With rsR
strLastNote = .Fields("Note").Value
Do Until .EOF
.MoveNext
If .Fields("Note").Value = "See Other" Then
'replace with previous value
.Edit
.Fields("Note").Value = strLastNote
.Update
Else 'value has changed
strLastNote = .Fields("Note").Value
End If
Loop
End With

MyQuery would be something like
SELECT Note FROM MyTable ORDER BY PatientID, VisitDate;

 

[Guest]

Hi John

I like it, but how do I check that the Patient ID from the Previous record also matches before the update? (If PatientID does not match the previous record it shouldnt update

Thanks for your help
Rober

----- John Nurick wrote: ----

Simplest to do this by iterating through a recordset. Air code

Dim dbD as DAO.Databas
Dim rsR As DAO.Recordse
Dim strLastNote As Strin

Set dbD = CurrentDB(
Set rsR = dbD.OpenRecordSet("MyQuery"

With rs
strLastNote = .Fields("Note").Valu
Do Until .EO
.MoveNex
If .Fields("Note").Value = "See Other" The
'replace with previous valu
.Edi
.Fields("Note").Value = strLastNot
.Updat
Else 'value has change
strLastNote = .Fields("Note").Valu
End I
Loo
End Wit

MyQuery would be something like
SELECT Note FROM MyTable ORDER BY PatientID, VisitDate

On Mon, 17 May 2004 17:11:02 -0700, RJErskin

An example of the table I am working with is below. The real table has 72 fields
“08976543â€,â€Smith, Johnâ€,â€333224444â€,â€031122â€,â€Sent Letterâ€,â€031208â€
“08976543â€,â€Smith, Johnâ€,â€333224444â€,â€031219â€,â€See Otherâ€,â€031219â€
“08976543â€,â€Smith, Johnâ€,â€333224444â€,â€040103â€,â€See Otherâ€,â€040103â€
“08976543â€,â€Smith, Johnâ€,â€333224444â€,â€040109â€,â€See Otherâ€,â€040109â€
“08976543â€,â€Smith, Johnâ€,â€333224444â€,â€040205â€,â€Ordered Recordâ€,â€040401â€
“08976543â€,â€Smith, Johnâ€,â€333224444â€,â€040211â€,â€See Otherâ€,â€040211â€
“08976543â€,â€Smith, Johnâ€,â€333224444â€,â€040314â€,â€See Otherâ€,â€040314â€

-
John Nurick [Microsoft Access MVP

Please respond in the newgroup and not by email