#1
|
|||
|
|||
Access or Excel?
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 |
#2
|
||||
|
||||
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] |
#3
|
|||
|
|||
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 |
#4
|
||||
|
||||
Hi Adam,
No workbook was attached to your post, so I can't comment on it.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
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. |
#6
|
||||
|
||||
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] |
#7
|
|||
|
|||
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 |
#8
|
|||||
|
|||||
Hi Adam,
Re: Quote:
Re: Quote:
Re: Quote:
Re: Quote:
Re: Quote:
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] |
#9
|
||||
|
||||
Quote:
Quote:
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 Quote:
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? Quote:
This has been very, very helpful. Thank you for your time and patience. |
#10
|
||||||
|
||||||
Hi Adam,
Quote:
Quote:
Quote:
Quote:
Quote:
Quote:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#11
|
|||
|
|||
Quote:
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. |
#12
|
||||
|
||||
Quote:
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] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel to lookup Access Database | PeterP | Excel | 1 | 06-11-2009 12:33 PM |
Should I uses Access or Excel? | tinkertron | Office | 5 | 03-29-2009 08:30 PM |
Excel or Access ? | inferno | Excel | 0 | 10-03-2008 03:42 AM |
Delete excel files by code in Access | Barry Richardson | Office | 0 | 06-13-2005 06:26 AM |
MS ACCESS - Import from Excel problem | Gregory_in_Montauk | Office | 6 | 05-06-2005 08:50 AM |