Hi Adam,

Re:
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:
You can generate quite complex, well-formatted reports by using MS Word and its mailmerge facilities for the report generation.

Re:
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:
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:
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.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]