Make Text field from many fields

[Amit K Garg]

I have Access 2000 database split in front and back end database. There is a
main Table PATIENTS with index field as AC. Many other tables are related to
PATIENTS table by the field AC. There is a table MEDICINES with multiple
fields like AC, Date, MedicineName, Frequency, amount, refills etc. Another
table exists by the name ALLNOTES with field names like AC, Date, Notes etc.
Appropriate forms exists for all the tables also.

When prescriptions are written for the patients on a certain date and many
medicines are prescribed using multiple fields AC, Date, MedicineName,
Frequency, amount, refills a prescription is printed for the patient.

I want to enter the information about the prescription details in a text
format in the table ALLNOTES with Notes field containing the composite
information from fields MedicineName, Frequency, amount, refills. Field AC
and Date will have the same data as MEDICINES. That way I can combine
multiple records from MEDICINES in one field and Doctors has to see only one
record In ALLNOTES to see the details of the medicines prescribed.

I am having trouble decidine as how to do that. Any help will be greatly
appreciated. Thanks in advance.

Amit

 

[Jim Ory]

Amit,

You may want to reconsider the following you wrote:

"I want to enter the information about the prescription details in a text
format in the table ALLNOTES with Notes field containing the composite
information from fields MedicineName, Frequency, amount, refills. Field AC
and Date will have the same data as MEDICINES. That way I can combine
multiple records from MEDICINES in one field and Doctors has to see only one
record In ALLNOTES to see the details of the medicines prescribed."

Your tables already contain all the information; you just need to extract
the data into a 'report' for each patient. No sense in loading up your
database with duplicate information.

Create a select query using the tables with the information you want the
doctor to see. All the tables will be linked via "AC". When you run the
query, it will extract the data from the tables. Save the query under a name
you can remember, such as 'qryDoctorsNotes'. Then create a 'report' using
'qryDoctorsNotes'. The report wizard is fairly good at helping you get
started as it adds a date when the report was created automatically.

Now this will undoubtedly produce a report with every patient entered into
your database. Not a particularly good thing. Amongst other ways, which are
going to be far superior, but to get started, in the query you made for the
report, in the criteria box under 'PatientsName' (or whatever the field name
is for the patient) enter "[Enter Patients Name]". (Be sure to add the
brackets.) When you run the query, it will prompt you for the patient's name.
Then only that patient's medications will be in the report. Better yet, due
to patients having the same name, use the patient's ID field and enter
"[Enter Patients ID]".

I'll restate that there are better ways to accomplish how to get your data
to the report, but this will work. Stay tuned for more advice from other
users.