Embedding data from multiple records in a single field in a table

[endocrin]

Patients have multiple doctors, but these change with time; Patients have
multiple letters which are kept permanently. I want to include in a field in
the letters that contains a list of all doctors attached to that patients
letter (a cc list) that is a snap-shot of that relationship at the time the
letter was generated. There is a patient file, a letter file a doctor file
and a patient doctor link file which enables multiple doctors to be linked
and the referral details and relationshsips recorded (this is used in an
oncology practice where a number of specialists and a family practitioner
look after the one patient)

 

[Duane Hookom]

Do you have tables and fields that you care to explain? You mention files,
letters, relationships, details,... but you haven't given any information
about you fields, tables, or data.

 

[John Welch]

It sounds like you should create a LetterDetail table, that would have these
fields at least:
LetterDetailID (PK)
LetterID (foreign key to letters table)
DoctorID (foreign key to Doctors table)
(Or, you could just make a compound primary key on LetterID and DoctorID)

This way you can store as many doctors as you want per letter, and the data
will be more flexible than having it all stored in one field.

hth
-John

 

[endocrin]

PatientID table, Referring Doctors Table, Patient Doctor Link Table, Letter
Table, Patient ID PatientDoctorId Doctor Id are the joining fields, and
letters are linked to patient ID Main recipient and cc are used in the
patient Doctor link table to determine the main recipient and cc's but
patients change doctors, others are involved for a time and so on. We need to
know permanantly who was linked at the time a given letter was generated.
Thanks for your interest
Peter Campbell

 

[Duane Hookom]

Have you considered creating a LetterCC table that links a single letter
record with one or more DoctorIDs?

Otherwise, if your Patient Doctor Link table had From and To dates and the
Letter table had a Letter Date then you wouldn't need to store any
additional information.

 

[endocrin]

The advice you have given describes the way we currently relate doctors,
letters and patients, but we make small alterations to the recipient list
with each letter. Although it seems contrary to good database design, I still
want a permanent snap shot in each letter of the recipient list; as the
letters run half to two A4 pages in a memo field, adding three lines of
doctor details is not going to make the database much bigger, and leaves me
with the information I need in one table; there must be a way to do this. We
have problems with data changing in other related tables as well; for example
the Australian medical benefits schedule of fees is updated every year and is
related by an item number; if I don't embed the fee at the time it is looked
up then when I look at the account for my patient next year it will have the
new fee and not the one we transacted; this is the same sort of problem; I
just want a way in code that I can put the result of a query with say three
lines in it, formatted with carriage returns into a single field. Is there a
way to do this? I though if the result of a query could be made into an array
(something I don't much about) then the information from that could be put
into a single field. Any suggestions?

 

[John Welch]

Well, I still don't quite see why having a separate table wouldn't work for
you. Each individual letter would have it's ID and then that separate table
would store doctors cc'd for that letter only. I don't see why this wouldn't
be a 'permanent snapshot' of the cc list for that particular letter, but
maybe I'm missing something.

Anyway, here is a rough sketch of how I would do what you are asking:
(untested)

Dim db As DAO.Database
Dim strSql As String
Dim rst As DAO.Recordset
dim strDoctorList as string

Set db = CurrentDb()
strDoctorList = ""
'make a query to pull the doctor names you want.,
'or use a saved query instead of strSQL

strSql = "Select doctorname from... "
Set rst = db.OpenRecordset(strSql)
Do While Not rst.EOF
strDoctorList = strDoctorList & rst!doctorname & vbcrlf
rst.MoveNext
Loop
'here add code to remove last carriage return from strDoctorList
' here add code to put the variable strDoctorList into your field
rst.Close
Set rst = Nothing

hth
-John

 

[endocrin]

Thank you,
I'm sure you don't miss much; I know how frustrating it is as a surgeon when
people don't take my advice. I will try both solutions you offer and see what
works best. The table solution would not keep the CC list exactly the same
(which is a legal record) if an itinerant GP changes his or her address to
another practice, so if we want to chase a hard copy of the letter or keep
sending to the same practice, this is useful. I suspect that one solution
would enable us to scroll through the letters faster than another, which is
useful as I and my colleagues have short attention spans. As usual I need to
fit this into the demands of my day job, so it might be a week or two before
I get this fixed
IRYHS
Peter