Update previous record (1 Viewer)

trab · Sep 24, 2018

I have imported the text of medical letters into an access table tblLettersText. Each line of the letter is stored in a memo field LineText.

Each letter is electronically signed, and I wish to put the name of the signee/clinician into a field Clinician. Sometimes the name of the clinician appears on the same line/record that begins with "Electronically signed by". In many cases (well over 1000) the name appears on the next record. I can pick a lot of these up, as the line begins with Dr. , Mr., Mrs. etc., but sometimes the name appears without a title, e.g. John Brown.

How can capture these, to update the Clinician field accordingly? Is VBA the only way to do it?

MajP · Sep 24, 2018

Is VBA the only way to do it?

I would think so since you need some fuzzy logic.
I think the logic is
1) Search for "Digitially Signed By" and search in that record for trailing name after "By"
2) If no trailing name go to next record
3) Then you need logic for what is a name. You will probably need several if thens to capture potential cases. Dr. Smith, Dr. John Smith, Dr. J. M. Smith, John Smith, John M. Smith.

Number three will be tough without seeing real possibilities to see different types and what if any logic could be applied.

MajP · Sep 24, 2018

Any chance you have a table with clinician information? That would make it a lot easier to look for potential names. Following "Signed By" is there other text or just the Name? If the name is on the next line, is there other text or just a name?

trab · Sep 24, 2018

MajP said:
Any chance you have a table with clinician information? That would make it a lot easier to look for potential names. Following "Signed By" is there other text or just the Name? If the name is on the next line, is there other text or just a name?

I do have a clinician table, but unfortunately, the text for the signee of the letters often differs from the text of the names shown in the table.

I'll try the VBA route then.

MajP · Sep 24, 2018

If feasible can you add a field to the clinician table for Digital Signature? That way if Dr. John M. Smith signs as D.M. Smith all the time you can still search for D.M. Smith but update your table with Dr. John. M. Smith or likely his unique Primary Key ID. That would simplify things a lot.

If you want help on this please post some sample data (change as needed for privacy) and desired output. Please post the names of the fields and tables. I got some ideas, but I think you will likely have to do this in some steps depending on how well structured your data is.

MajP · Sep 24, 2018

Without seeing the data, it will be hard to say if you can do it in pure sql. If there is a way to sort the table there is a way to join a record to its previous/following record. Then you could do a criteria with like "*Digitally Signed By" and join that to its following record. If that following record is nothing but a name field and nothing follows "Digitally Signed By" then you may be able to do this in pure sql.

Update previous record (1 Viewer)

trab

Registered User.

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

trab

Registered User.

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

Similar threads

Users who are viewing this thread