How to maintain patient medical records to generate reports

[Mallela Hari Krishna]

Guidance is required in maintaining patient records for generating reports on the basis of diagnosis to list out the patients suffering from the same disease and medication prescribed .so that we can use pivot tables for summarising the data.

1. we need to run a report based on the diagnosis column mentioned 2 or 3 diseases, where the same person is suffering from multiple diseases, in a summarised way.
2. To Analyse the treatment given for the diagnosis made



ht tps://1drv.ms/x/s!Aiom8AvW0X87izJDVgrxpP4niQ95?e=YERZdP

 

[vletm]

Mallela Hari Krishna
Of course, Your given data is sample data or how?

 

[Mallela Hari Krishna]

It's a data being collected by me, I would like to generate a report based on the above data "Diagnosis column" in this column I had mentioned the patient is suffering from 2 or more diseases but I would like to generate the report that "disease wise " how many patients are suffering from one disease. To summarize by the end, guide me on how to maintain data in the best way. I'm facing difficulty summarizing based on each disease wise.

 

[Mallela Hari Krishna]

It's a Sample Data

 

[vletm]

A sample data do not have collected from any real data, which has real information in those columns.
After You'll have fake data,
You also should show sample of expected reports.

 

[Mallela Hari Krishna]

Sorry, Dear!
I'm doing this to my cousin for maintaining a database for the patients diagnosed by her.

Not to fake the report she wants to maintain a database for types of diseases treated by her in a structured way.

The report is mainly to submit to the Principal physician.

 

[vletm]

Mallela Hari Krishna
#1 Data ... for many ... own medical information are not public information.
...that's why, only sample ( = fake ) data to public sources.
#2 I checked Your file
Here, one part of sample from it.
For my eyes, there are many 'diagnosis' and many of those has named there once.

 

[Mallela Hari Krishna]

Could you please guide me on how you have done...

 

[vletm]

I sent a sample for Mallela Hari Krishna

 

[p45cal]

reports on the basis of diagnosis to list out the patients suffering from the same disease

Pivot table at cell A1 of the Summaries sheet.

report that "disease wise " how many patients are suffering from one disease.

Pivot table at cell F1 of the Summaries sheet.

reports on the basis of diagnosis to list out the patients suffering from the same disease and medication

Pivot table at cell K1 of the Summaries sheet.
There's also a pivot giving how many patients on medications at cell P1

These pivot tables are based on two Power Query queries; one query splits the DIAGNOSIS column into new rows using a comma to delimit the different diagnoses and the second query which splits the CURRENT MEDICATION column in the same way.
You can add charts to each pivot table if you want.
The pivot tables show up many variants of the same diagnosis and medication, so you'll need to correct the spelling on the source data to get more meaningful results.
It will be very difficult to tie the diagnosis with medication as there is nothing in the source table linking them.

 

[Mallela Hari Krishna]

Pivot table at cell A1 of the Summaries sheet.

Pivot table at cell F1 of the Summaries sheet.
Pivot table at cell K1 of the Summaries sheet.
There's also a pivot giving how many patients on medications at cell P1

These pivot tables are based on two Power Query queries; one query splits the DIAGNOSIS column into new rows using a comma to delimit the different diagnoses and the second query which splits the CURRENT MEDICATION column in the same way.
You can add charts to each pivot table if you want.
The pivot tables show up many variants of the same diagnosis and medication, so you'll need to correct the spelling on the source data to get more meaningful results.
It will be very difficult to tie the diagnosis with medication as there is nothing in the source table linking them.

Thanks a lot, dear!