Is a macro what I need for this?

[davidjearly]

Hi,

I have a patient database with data stored in two tables. One form is used for entering personal details (First Name, Last Name, DOB, Patient Code, Sex, Address, Phone Number ect) for a patient. This is stored in the table called 'Patients'. Another form used for entering treatment data (Treatment date, Treatment Type (Assessment, Review 1, Review 2 ect) and 25 combo boxes for 25 standard assessments that contain scores from 0 to 10) which is stored in another table 'Treatments'.

I would like to have a button on the form that would export this data to excel with column names based on the 25 different assessments and row names based on whether it was an assessment or review. Obviously this would have to take into account what patient I was currently viewing so that only data for the patient record currently being used would be exported to an excel sheet. I think I would do this using the patient name as this is stored in the 'Treatments' table.

Is this possible? If so, is it a macro I would use for this? Lastly - any tips on how to go about creating this would be appreciated.

 

[Rabbie]

There is presumably a PatientID field in your Treatment table? If so you should be able to write a query that will show the treatment data for a particular patient. You should then be able to export this to Excel..

 

[Rabbie]

ON re-reading your post I some problematic areas in your design. A patients name is not necessarily unique - Your could have more than 1 John Smith for example. Much better to have a unique patient ID in each record. Your method could result in patients getting the wrong treatment.

Also you need to read up on Data normalization. It is not a good idea to have a lot of repeating fields in a table.

See these links for more info

http://www.datamodel.org/NormalizationRules.html

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

 

[davidjearly]

There is presumably a PatientID field in your Treatment table? If so you should be able to write a query that will show the treatment data for a particular patient. You should then be able to export this to Excel..

The Client Code is stored in the Patients table. Is there a way I can also store this information in the Treatment table when it is entered into the Patients table via the Patients form?

Unfortunately, I can't pull the database apart as I didn't design it and the department is reliant upon it. I know that parts of the database don't follow good design however.

Nevertheless, it functions and I would just like to add this functionality to it. If I can get the Patient ID field to show in the 'Treatments' table when it is entered in the 'Patients' table (it has to be stored there to as other parts of the database depend on it), I might be able to have a go at what you suggested.

Thanks for your input.

 

[davidjearly]

I have a query called 'Patients' containing fields 'ID' (primary key from table 'Patients') and 'Client Code', also from the table 'Patients').

Is there a way I can use a SELECT statement in the row source for field 'Client Code' in the table 'Reviews' to get it to update with the client code?

I have tried this:

SELECT [Patients Extended].[ID], [Patients Extended].[Client Code] FROM [Patients Extended];

But when I view the table in datasheet view, it just gives me a drop down menu with a list of all the client codes in the database rather than actually linking the correct one to that record.

Thanks again.

 

[davidjearly]

I have a query called 'Patients Extended' containing fields 'ID' (primary key from table 'Patients') and 'Client Code', also from the table 'Patients').

Is there a way I can use a SELECT statement in the row source for field 'Client Code' in the table 'Reviews' to get it to update with the client code?

I have tried this:

SELECT [Patients Extended].[ID], [Patients Extended].[Client Code] FROM [Patients Extended];

But when I view the table in datasheet view, it just gives me a drop down menu with a list of all the client codes in the database rather than actually linking the correct one to that record.

Thanks again.