Forum Discussion
Solved
best practice for excel Data base
Hello,
I have the workbook that contains information about 20 health Centers and mobile teams that we collect the data from them in weekly basis.
I would like to know the best practice to accumulate the data in the workbook and the manipulate and use this data to generate reports and charts for decision makers.
many thanks in advance
Jihad Al-Jarady Well, then you could consider to stop doing the intermediate summary by week and work off a table that may contain max 140 rows (20 centers X 7 days) per week, as long as every row has a date to that you can "calculate" the weekly numbers.
Jihad Al-Jarady Not sure if it conforms with best practice, but if I would have to set this up from scratch, I would collect data in a tabular format, and have your health centers record every single visit/consultation with their relevant attributes. Create a table with columns for e.g. date, time, center, gender, age, diagnosis, new/follow-up, hospital referral Y/N. Join them all together with e.g. Power Query. You may end up with thousands of rows of data over the year, but it will not be much of a problem for Excel to work out the week numbers, the age groups and summarize it all by week, by diagnosis, by center, by district, by age or whatever way you want.
The workbook you have now has what I would call some classic design faults that makes analyzing/summarizing/filtering near to impossible. To name few:
Merged cells, multi-row headers, far to many columns (who could ever grasp/oversee 440 columns?), columns with sub-totals, hidden columns.
I appreciate your quick response.
So, If will create it from the scratch, it would be better to separate the information into tables then connect them together with a relationship?
I create a table in worksheet Consultation, then I entered the record 4 times to differentiate it,( 2 records for Male and 2 for Female, the for each one for new and one for follow up) see attachmentWhat is the best way to deal with table that has merge cells?
What do you mean by "connect them together with a relationship" ?
But if I would take your "Consultation" sheet, I'd connect to it with Power Query (PQ), unpivot the data and create a table as shown in Table1 and then a pivot table from that one.
The attached file contains a very crude example.
With regard to dealing with tables that have merged cells, you can handle it PQ, but that would be extra work. Better to avoid them all together.
