suggestions for creating useful case id

[mishmish3000]

Good morning all!!! Has everyone dug themselves out of the snow?
Here's something I wanted to send out to the group for your thoughts and ideas... I'm dealing with an Excel-based data collection system (I refuse to call it a database). The system keeps track of mothers, infants and contacts within the family that the mother has had... the mother is positive for hepatitis B. Keeping track of hep B pos moms is very important b/c if we can immunize the baby within 12 hrs of birth, the baby can be hep B free. Regional nurses find out about cases and enter their info into two spreadsheets--one for moms and their babies, one for mom's contacts. Each mom+baby combo and each mom+contact combo gets a unique case ID. So far, the system has been using these two sheets for each birth year--i.e., two sheets for 2009, two sheets for 2010, etc. all based on the baby's date of birth (DoB). Consider that we have 13 health regions. We're now tracking 2009, 2010, 2011, and soon 2012. That means we have about 8*13 or 104 spreadsheets to track. This is getting crazy!
My idea is to do the following:
1. Forget separating the spreadsheets by year. Instead, have all mom+baby data in one sheet, all mom+contact data in another sheet, for each of the 13 regions.
2. Come up with a more logical, more efficient way to give the cases their IDs. Right now, the nurses have to call or email the central office to get each new case ID, from a list the hep B coordinator keeps on her computer. The IDs have the following structure now: YYYY-9999 where YYYY is the birth year of the baby and 9999 is a sequential number for the case. The first case of 2012 will be 2012-0001, for example.

Any suggestions on coordinating the spreadsheets? The regions really aren't supposed to see each other's data, so we can't have just two master sheets where all the regions enter all their data, sadly. So we're "stuck" with having a workbook for each region, with at least two sheets in it.

More specifically, can you think of any better way(s) to give each new regional case a logical, unique case identifier? Preferably using some sort of autonumbering system where the regional nurses wouldn't have to call or email upon discovery of each new case? I was thinking maybe there would be a way to put the regional abbreviation in the autonumbering scheme, so we wouldn't have to worry about duplicate numbers. The regional abbreviations are: WTR, SHE, MCR, SCR, SER, UCR, ETR, NER, NDR, CHR, KKR, JMR, and SUL. So maybe there's a way to include them in the autonumber? I don't know.

Please give me your thoughts. While almost everyone is out of the office over the holidays, it gives me a great window to explore better, more efficient data management tools for these health folks. They're doing well to use Excel... otherwise, I'd just switch to an Access database. But they're not ready for that (yet, if ever). So we're stuck with Excel...

Thanks! Sorry to blather on so long.
MishMish

[HansV]

The simplest solution seems to be to use a sequential number 1, 2, 3, ... within each region's workbook. Let's say this number is entered in column A, starting in A2 (row 1 contains field names/column headers). If you wish, you could pre-fill A2 through A1000 or so with 1, 2, ..., 999.
In B2, enter a formula =IF(A2="","","WTR"&TEXT(A2,"0000")) where WTR is the region code. If this code is stored in a cell, say K2, you could use =IF(A2="","",$K$2&TEXT(A2,"0000")). This formula can be filled down as far as needed. It will result in IDs such as WTR0001 that can be used when combining the regional sheets.

[mishmish3000]

Let me try that out and see how it works! Thanks!! Looks super.

What do you think of the idea of having only one workbook with the two sheets in it... as opposed to 12 gazillion worksheets floating around?

LOL
MishMish

[HansV]

That is certainly easier to manage than a separate sheet for each year!

[HansV]

Another option would be a central database with a web interface. There'd be only one table for all babies, but each region would only be able to see and edit their own data, and perhaps view statistical (aggregated) summaries of all regions combined.

[mishmish3000]

Yes, a web based app would be best... unfortunately, due to IT issues, regional facilities sometimes cannot have access to a central web-based application. (It's complicated, having to do with the fact that some regions are "metro" regions, with their very own IT staff and rules, and other regions are "state" regions. The "state" regions could all access a central web-based app, but none of the "metros" can.)
So we're stuck with emailing spreadsheets to individual regions, having them fill them out, and then having them send them back to us at the central office.

[mishmish3000]

Hi BobH!
I wasn't sure how to reply directly to you, but I thought you raised some great ideas in your email to me, so I'd share with the group. First, the spreadsheets are encrypted and also sent through a secured email set up. Secondly, this system's been in place for several years and is official, and officially approved. It's not an unofficial, unapproved data repository. I'm just trying to improve it. But the HIPAA and other privacy issues have been addressed. I certainly appreciate you asking about that! It's an important issue. Basically, my position is to look at existing systems, often ones set up by non-data management types, and improve them. That's why I felt I could ask the Eileen's Lounge folks some generic questions to see if there were some extra good ideas floating around out there. Of course, I never share actual data with Eileen's Lounge users, just general data fields and sometimes completely made-up data as examples.
Hans V had mentioned a web portal, and that would be ideal, but as I explained in an earlier post, we can't do that just yet since some of the regions are differently set up, IT wise, than others.
Thanks for your great comments and ideas.
MishMish

[HansV]

But for a database with a web interface, the end user only needs an internet connection and a browser, there is no need to install specific software locally. I assume that most, if not all medical facilities have internet access nowadays...

[mishmish3000]

true, very true... but the catch is, as I understand it, some of the regions are not allowed to access State intranet sites, and we can't post the portal on an Internet site, due to HIPAA (US hospital privacy laws). So if everyone could get to the InTRAnet, we'd be fine. Or if everyone could use the InTERnet, we'd be fine. But neither is workable at this time. I'm going to research and see why, exactly, some of the regions (the metros) can't access the State InTRAnet. it's frustrating.

HansV, your code was MOST EXCELLENT! Thank you many times over!

MishMish

[BobH]

true, very true... but the catch is, as I understand it, some of the regions are not allowed to access State intranet sites, and we can't post the portal on an Internet site, due to HIPAA (US hospital privacy laws). So if everyone could get to the InTRAnet, we'd be fine. Or if everyone could use the InTERnet, we'd be fine. But neither is workable at this time. I'm going to research and see why, exactly, some of the regions (the metros) can't access the State InTRAnet. it's frustrating.

HansV, your code was MOST EXCELLENT! Thank you many times over!

MishMish

Ah! The bureaucracy of the workplace and the protection of turf!

I understand your predicament and your frustration, mm3k. I'm glad to see that my remarks were not ill-received. You have already 'plowed that ground' as we say in Texas. From this post I can tell that you are very much on top of the issues and the game. Hans has already given you better counsel than can I. You have my sympathy but I know that it helps not at all. Perhaps some political pressure can be brought to the state system to allow intranet access to the regional users. Surely Citrix or some other agent could be used to provide the necessary end user control. Or, as I sometimes used to discover, perhaps the 'why not' question has not been heard by the 'right' set of ears.

Remember that Admiral Grace Hopper, USN (dec.) used to say, "It is easier to get forgiveness than permission!"

Good luck!

[sdckapr]

Why not put a file on an Internet site that does not put any personal information? It would only assign the next unique ID and the date and (perhaps) site location requesting it. This should not have any privacy issues and everyone could still maintain whatever they needed in terms of files.

Steve

[mishmish3000]

Yes, the ogre of bureaucracy... and conflicting email systems. Gee, who HASN'T run into that at some point? Anyone? LOL

This has been so helpful, thank you to everyone who has commented or contributed.

Steve, ideally we'd like to have an internet site where the nurses could access a password protected Access or SQL Server database, enter in patient information, and then the perinatal hepatitis B coordinator (another nurse) could access reports once a month on the 13 regions. However, we can't get there from here.

Using an internet site to assign case IDs is an interesting idea, however, with the way the State is structured IT-wise, it would be much more work, pain and suffering than is needed or tolerable. I think we'll stick with Excel workbooks for each region, with autonumbering for case ID assignment. We can deal, I think, with 13 Excel workbooks once a month being emailed securely to the central office... rather than the current situation, where we have 100+ Excel worksheets being sent (often separately), zipped, to the coordinator. The poor gal's pulling her hair out right now...

In an ideal world, we'd be able to create, test and put a web site into production that would allow the nurses to enter their data in a database form that looks like the paper ones they're used to, and also allow the coordinator to see monthly and yearly reports on the data. Eventually, maybe, we'll get there but for now, we're sort of stuck with good ole Excel.

Thanks again, everyone! MishMish