Hi Adam,
Re:
Quote:
|
1) it's easier for the end-user to see some of the data from the first sheet when filling out the second sheet
|
If you were to provide the user with a data-entry form, there would be no need for them to see either sheet. You could, for example, have combo boxes for inputting/finding patients via MRs or surnames, all linked to the workbook. Records could be added where no match was found and other fields could be auto-populated (to the extent practical) from the fields for matched records.
Re:
Quote:
|
2) it's necessary for reporting purpose
|
You can generate quite complex, well-formatted reports by using MS Word and its mailmerge facilities for the report generation.
Re:
Quote:
|
MR # and admission date ... together (but not separately) represent a unique identifier
|
The problem I see with this approach is that, if the same patient presents more than once, much of their existing record needs to be replicated and that's where the potential for data-entry inconsistentcies, as a result of which, considerable effort might be needed later on to clean up the data. Having one MR#/patient and multiple 'events' with which that MR# is associated is indeed the way to go (bear in mind that a patient might even present twice on the same date - I did once - so a date identifier may not be sufficient).
Re:
Quote:
I'm assuming it'll be something like this:
Table 1 - Client Demographics (name, mr #, DOB - things which shouldn't change)
Table 2 - Admissions (mr #, admit date, admitting diagnosis, hospital info, etc)
Table 3 - Outcomes (filled out upon new assessment period or discharge)
|
you might find it beneficial to have a separate table for hospitals, and maybe another one for doctors, etc too, so that these details can be looked up, rather than having to re-input them each time.
Re:
Quote:
|
I want to verify that I'm on the right track
|
From what you've described so far, I'd say so. In a well-designed relational database, you'd have multiple tables each linked to others by only one or maybe a few fields, to keep data redundancy to a minimum.
I mentioned mailmerge above, but Access has extensive reporting facilities built in (though the output formatting isn't as rich as Word's). Be that as is may, any investment you might make in creating input forms for Excel and Word mailmerges won't be a wasted effort. Much of the input Excel forms will be re-suable in Access, as will any Word mailmerge documents. The coding behind the forms would need re-working to address db tables and the mailmerge document would have to be pointed to the db and its fields, but the basic userforms and mailmerge documents could remain pretty much the same.