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]

Why would I use Word? Are the Reports found in Access lackluster?

You're right - the DOB, first/last name, etc. could be inconsistent. However, some of the other data on the spreadsheet is specific to a particular admission. That's why having at least three tables in Access - one for the client's static data, one for the data specific to an admission date, and another for the assessment periods tied to an admission date - makes a lot of sense to me.

You have a fair point regarding the same patient entering twice on the same day. As a nursing home, however, we can only bill once, so we'd only count the admission we'd be billing for

If I had a table on the hospitals or doctors, it'd literally be a one-column list of hospitals or doctors. The hospitals and doctors are specific to the admission date, so it'd be the same hospital and doctor through the assessment periods tied to an admission date. Would there still be benefit to giving them their own tables?

1001 John Smith was born on 1/11/1935, lives at 10 Main St - this information is on Table 1 and never changes

1001 John Smith had an admission on 1/1/2011, he came from ABC Hospital and was assigned to Dr. Miller. His insurance was Medicare. This information is static through the entire admission (Table 2)
1001 John Smith had another admission on 4/1/2011, when he came from BCD Hospital and was assigned to Dr. O'Brien. His insurance switched to Harvard Pilgrim. (Table 2)

1001 John Smith had a 5-day assessment, a 14-day assessment, and a 30-day assessment done for his admission on 1/1/2011. There is data specific to each assessment (reimbursement rate, last date of assessment period, etc). (Table 3)
1001 John Smith switched his insurance prior to his second admission, and his stay is no longer divided into 5-day, 14-day and 30-day assessment periods. Table 3 would be filled out only following discharge.

That was the original flow, at least. Maybe I should rethink it?

Can you explain what you mean by mail merge? Would this just be a way to create input forms in another program (because they look better?) and then using them in Access? For someone as new as me, would I be better off doing everything (user forms, reports) in Access for the time being? Or am I missing something?

This has been very, very helpful. Thank you for your time and patience.

Hi Adam,
It's really a matter of what you want from the reports and how often you're be producing them. In some ways its like comparing Word and Excel. Word produces much documents with much richer formatting flexibility than Excel can, but Excel's got many built-in functions for number-crunching. Similarly, Access is great for data storage & retrieval - and has some good reporting and analysis tools, but neither is as comprehensive as Word for document-generation or Excel's for the number-crunching.
Yes, i can't really envisage your clients coming & going twice in a day - maybe over a month ...
The benefit lies in being able to retrieve data by hospital/doctor when doing data entry (you don't have to input all the details every time) and for subsequent reporting. If you know they're in the data in a consistent format, that makes retrieval much simpler.

Mailmerge is a means of extracting data from a spreadsheet or database and dropping it into a document. Useful when you want to produce things like mailing labels, standard letters (eg monthly statements), client lists etc.
No. Mailmerge is for data extraction, not input.
ell, since you don't already have the Access db set up and running, no. You could, however, develop the Word mailmerge documents for use with Excel now and simply change their data source to Access later on. It's really quite straightforward to do this - possibly less than 5 min per mailmerge main document.

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