Hello:

I work for a mid-sized healthcare company which runs a variety of senior care companies, including nursing homes, a home health agency, a hospice, etc. We would like to expand our data tracking and reporting capabilities for reasons of both internal quality control and external marketing. While I have begun this in Excel, I would like help in determining whether or not this project would best be done in Access.



By way of experience and background, I myself am an intermediate user of Excel and a beginner user of Access. My only exposure to Access was during a 3-hour hands-on course I took yesterday. I am very comfortable with computers in general and I pick things up quickly. The end-user who will be entering data into these workbooks/databases will mostly likely be a beginner user of Excel and will have no experience with Access.

In regards to the data I'm looking to store as well as the queries and reports I'd like to generate from the data, let me give you an example of the worksheet I've created in Excel for one of our nursing homes:

There are two worksheets. One contains the client's static information for a given admission date, and would be filled out soon after admission. This data includes:
Medical record #, name, admission date, insurance, DOB, primary diagnois, referral source (hospital), screening source, primary care physician, # and type of wounds admitted with.

The second worksheet would be updated upon two events: the start of a new asessment period for the client (short-term stays are divided into Medicare-defined 'assessment periods'), and upon discharge. Information recorded in this sheet includes:
Reimbursement rate during a specific assessment period, date of discharge, # and type of wounds healed or acquired during stay

I would like to use this data to answer queries and to generate reports. Examples of queries include:
-How many clients with diabetes were referred to us by a given hospital between January and March of this year?
-How many/what % of clients came to us from a particular area?
-How many pressure ulcers did we heal in December 2010?
-What is the difference between inaverage reimbursement rate during a client's second assessment period and their first?

Examples of reports include:
-A monthly report for a particular hospital, which would include the following information: # of patients referred by hospital during that month, their clinical outcomes (number of wounds healed, etc), etc.
-A diagnosis-specific report which would show clinical outcomes, where clients with that diagosis were referred, etc.

Thanks to some fancy code supplied by various friends and message board colleagues, I'm able to answer those queries and generate those reports (though I have not created them yet) using Excel. However, I am starting to wonder if Access is best equipped due to the relational status of some of these questions.

I guess my main question is this: Am I best off continuing to do this project in Excel, given the fact that I 1) am familiar with the program, and 2) can conceivably do what my current desired ends are using it, or are the realtionships sufficiency complex enough to warrant me jumping head-on into Access?

Thank you in advance for any guidance and assistance.

Adam

Hi Adam,

Your requirements would be better met, especially in the longer term, with Access or a similar product.

The fact that the user might have little or no experience with either Excel or Access is inconsequential, since what you really need is a series of interfaces (eg userforms) for data entry and report specification. With those, it really doesn't matter to the user what the background application is - for all they care what's sitting behind it could be Word or PowerPoint, or a stand-alone application.

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

Thank you, Paul. This all makes sense to me.

I'm less worried about the end-user's experience level (which, as you pointed out, does not matter a whole lot) than I am my own. One thing I did not state in my original post is that the Excel workbooks I've completed are end-user ready, and until yesterday's Access course I was ready to roll them out. For a variety of reasons, I do not want to wait another couple of months for the end-user to do the data entry. The reports and queries can wait until I'm more familiar with the program, but I'd like to start storing the actual information itself.

Now, I understand that Excel tables can easily be imported to Access, and I have no doubt that the first worksheet, which contains the static client demographic information, could be used now and imported to Access later. I'm uncertain, however, as to how Access would handle the second sheet.

For that reason, I'm attaching the workbook which is end-user ready for our nursing homes. (No private or protected information was used). As you can see, there's some VBA (written by colleagues, edited by me - I'm not fluent!) which right now is "forcing" Excel to act corectly Again, the way it works is as follows:

Upon admission, client data is inputted into the worksheet 'Client Demos'
At the end of a patient's assessment period, or after they're discharged from short-term care, a new entry is added to the 'Outcomes' worksheet. The client's MR # is entered into column A, which then creates a dropdown box in the corresponding cell in column B which lists the client's admission dates. From there, columns d:w are filled in. The user then enters the assessment period/type, the "RUG Category" (corresponds with reimbursement rate), whether the reason for entering the data is because of a new assessment or discharge, and finally the date of new assessment/discharge in columns c, w, x and y respectively.

With all of this in mind, can the second sheet's information - with tweaks - be imported safely to Access from Excel once the Access database is up and running? Or would I be best off importing the first sheet only, and having someone do a one-time major 'catch-up' data entry when we're ready to convert from Excel to Access?

Thank you again for your assistance.

Adam

Hi Adam,

No workbook was attached to your post, so I can't comment on it.

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

Ah, I did not realize that I had received an error message because of the file extension (xlsm) when attempting to upload it. I've saved it as a macro-free workbook.

On second thought, if you're using Office 2000, will you be able to open this xlsx file? I tried saving it as an Excel 97-03 file, but there was a significant loss of functionality.

Attached Files

Tracking Final Sample.xlsx (49.3 KB, 16 views)

Hi Adam,

My first observation is that your worksheets have a lot of duplicated, hence redundant data. Provided each client has a unique ID, that is the only datum required on all sheets. Aside from anything else, this reduces the risk of inconsistencies between the two sets of data. In this regard, you'd probably do best to have one sheet with the details of each client, then whatever other sheets you need for, say, referring agency details and for tracking clients' history. As it is, though, if you're going to have a separate record for each 'event', you could probably have the whole lot on one worksheet.

Whichever way you go, I don't think there'll be a significant issue exporting the data to Access - provided you eliminate the data duplication. Access can import Excel workbooks and various other files formats, including CSV to which Excel can output data.

As for reading the file, I have both Office 2000 and 2007 installed. In any event, Office 2000 can read Office 2007 files courtesy of a format converter from MS.

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

Thank you. The reason for the duplication is two-fold: 1) it's easier for the end-user to see some of the data from the first sheet when filling out the second sheet, and 2) it's necessary for reporting purposes. I know of no easy way in Excel to create the reports I referenced in my original e-mail without duplicating the data. I do not believe there to be a real risk of inconsistencies, as the duplicated data on sheet 2 are all copied over directly from sheet 1 through vlookups - there's no repeated data entry. Enter the MR #, select the admission date, and the data entered in sheet 1 appears on sheet 2.

That being said, it appears that Access is structured so that there's no reason for this duplication. So, what I'd probably do is import the second worksheet without the duplicated data. The only link between the two sheets would be the first two columns: MR # and admission date. Those two together (but not separately) represent a unique identifier. I could see MR # becoming a unique identifier on its own in Access, and linking each MR # with (potentially) multiple admission dates, and then linking that date with the variable information related to each admission date. I'm not sure how I'd do all of this yet, but I'll get there. At least all of the data will be there!

Because of my inexperience with databases in general, I still haven't come up with a plan for how everything will work in Access. 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)

Doing it this way would require an extra table (the information on Table 1 and Table 2 are combined in the Excel workbook, but doing it the same way in Access would lead to redundancy), but I'm not certain that this is a problem (or if this is the solution, for that matter!). I'd then be able to create the queries and reports involving data from all three tables. So I'd be able to determine, for example, how many discharges (Table 3) of patients born between 1935 and 1945 (Table 1) originally admitted from Mass General Hospital (Table 2) we had in March 2011 (Table 3).

I don't mean to bore everyone with this stream of consciousness - I want to verify that I'm on the right track.

Adam

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]

Details of what? I apologize - I don't mean to be obtuse. We would store no data except for their name on the hospitals and doctors in this database. So we wouldn't, for example, store the doctor's address or the hospital's phone number. I'd plan on creating a lookup with fixed values (drop-down box) to ensure consistency. If I were to create new tables for hospitals and doctors, why would I not create tables for every other column which has a pre-set number of options (such as the wounds or infections columns)?


If I understand Access' reporting capabilities (and I may not!), I don't think I'll have to do mail merges right now. Our health care-specific accounting package has all of the information needed for mailings, and since mass mailings come out of the business office, they'd continue using that program. This project is not meant to replace that program but rather store data and create reports otherwise unavailable to us.

What I was planning on doing, for now, was just running with the Excel workbooks and then importing them to Access when I had the forms ready. (I'm comfortable that the data entry folks can use the bulky spreadsheet I created!). In fact, our hospice has already started using the workbook I created for them. In giving it more thought, I can see why it's necessary to interface with both programs. Our hospice workbook automatically calculates the next assessment period, which is vital for hospice operations, and I doubt there's a way to comfortably replicate these calculations in Excel. Maybe a linked workbook is the answer, I don't know. I have a lot of researching to do!

Also, there are some sample reports hidden in the Excel workbook I uploaded. While the links are broken (the reports were designed early in the process), you can see what I was trying to do.

Well, if you're planning "on creating a lookup with fixed values (drop-down box) to ensure consistency", that's a definite candidate for a table in its own right. I imagine your 'catchment' in this regard is for the most part fairly limited, so the number of entries to go into and be maintained in the table would be relatively small.

And, yes, you could create another look-up table for 'conditions' but I imagine there'd be so many variations compared to the number of patients that the effort in creating and maintaining it would outweigh the potential benefits.

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