Updating Records from Previous Record

[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
Thanks

 

[Rob]

Hi,

You need to create a many to many relationship between Patient and Notes
tables. To do this in Access you will need to create a link table. For
example

tblPatient - PatientID (PK), Name, SSN etc
tblNoteLink - ID (PK), PatientID, NoteID, Date
tblNote - NoteID(PK), NoteComment

This relationship allows you to create notes (in tblNote) and assign the
same note to patients as many times as you want.

In tblNoteLink you will have to ensure that the Indexed property of
PatientID and NoteID are set to "Yes (Duplicates OK)"

Cheers
Rob

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.