0

I'm looking for help on how to approach a problem, rather than a code solution. I'm a military pediatrician trying to make an Access desktop DB/application that would make it easy to identify patients who have special needs, but are not enrolled in the special needs program, and then automate some of the drudge work in enrolling them.

I have 2 Excel spreadsheets with patient data. One is a list of all my patients, the other a list of patients already enrolled in special needs. I don't have any control over the exported content, so we have to work with what we get:

All-Patients:

  • Patient Name ("Last, First M") (string)
  • Init Last 4 ("A4873" - first letter of last name, last 4 of sponsor SSN) (string)
  • Home Phone (string)
  • Age (int)
  • DOB (date)
  • Sex (M/F) (string)
  • Active Duty (Y/N) (string)
  • Provider (string)
  • Address (string)

Special-Needs:

  • First Name (string)
  • Last Name (string)
  • Middle Initial (string)
  • Sponsor SSN (only if Role = Sponsor) (string)
  • Role (Sponsor, Spouse, or Child) (string)

CONSTRAINTS: MS Office 2013 in a high security environment; addins and macros are disabled. I can run any VBA code I include myself, and include references already present on the system. 3rd party packaged solutions are not allowed.

Step 1: Import both of these into a common table with all my patients AND their enrollment status. This will need to be repeatable, as the source data will be updated once each month.

PROBLEM 1: No common identifier. I was able to split the name field from the All-Patients into LastName, FirstName; however, the FirstName (which is really FirstName + Middle) won't necessarily match the other list's first name, and I can't guarantee that a name will be found on both lists due to sloppy data leftovers related to people moving around frequently in the military.

The only data that could be used to make a unique, matchable key in each is some combination of name and the sponsor's social security number. All-Patients has InitLast4, so we can at least get the last four digits of the sponsor's SSN, and Special-Needs has the full sponsor SSN. If you see other ways to make unique matchable keys out of the above data, shout it out!

PROBLEM 2: Special-Needs only includes the sponsor SSN in the records that are actually sponsors, so you end up with something like this:

LastName   FirstName      SSSN      Role
---------------------------------------------
Jones      Mother                   Spouse    
Jones      Daughter                 Child
Jones      Father      555-55-5555  Sponsor
Jones      Son 1                    Child
Jones      Son 2                    Child

In order to use the sponsor SSN as part of a key, it has to be filled in for the other family members. All of the "Fill missing data" tutorials I found talked about a FillDown approach where the data was sequential, which won't work here - the sponsor is not ordered relative the rest of the family. There's also no guarantee that the sponsor has the last name as the kids, but without alternative key ideas, it's an infrequent and acceptable loss of data.

I assume the solution here is to fill it programmatically with VBA, through some combination of loops or queries, but I don't know how to approach it in an efficient way. One option is to sort by last name, store that last name, loop through and add each record with that last name to a temporary recordset, loop through that to identify the sponsor SSN, then loop again to add the data to the records that are missing it. This dangerously depends on there never being multiple families with the same last name - e.g., it couldn't distinguish between two families with the last name Brown, and would assign the SSN indiscriminately.

Another option would be to query Special-Needs for all records that have a SSN, then some sort of update query that would match the last name and fill the SSN, with the same last-name discrimination issue above.

CONCLUSION: If I can reliably combine these data sources (in a repeatable, easy manner), the remainder of the application is trivial (basically click a button and send an email with the person's information to the special needs coordinator). If the only solution is labor-intensive, then we haven't gained much over the current workflow (manually scan the list for a patient's name to see if they're on it, and if not, write an email to the coordinator).

Again, although direct solutions are welcome, the most useful help is in how to approach this problem. I'd appreciate any help you can offer!

pediatrictactic
  • 103
  • 11
  • Yes, but they are non-unique values. Every person within a family will have the same InitLast4 (because they share the same last name and there is only one sponsor SSN per family). – pediatrictactic Mar 30 '16 at 20:05
  • Immediate goal is to have a single table with all patients, with an additional field (e.g. "Enrolled-In-Special-Needs"). If a patient is listed in the Special-Needs data source, then they are already enrolled. The Special-Needs source is organized by patient; the person making the file only puts the sponsor SSN on the sponsor, instead of all family members, so I'd like to propagate that SSN to the family members. InitLast4 comes into play since the last 4 digits of the sponsor SSN, and the names, are the only things common to the imported data. – pediatrictactic Mar 30 '16 at 20:31
  • Because the sponsor's position varies in the data, I can't assume that reaching a record with a sponsor SSN "starts" a family section - the records both before and after it may be part of that family, so navigation gets confusing. – pediatrictactic Mar 30 '16 at 20:32
  • instead of setting the SSN of the family member with special needs, would it be better if you set a unique ID for every family. That way you can have as many special needs members being tied to the family ID as well as attaching more members of the family to the family ID. However, this means that for every new patient, you have to ask them if any of the family members are already in the system . Whether they say yes or no, you would have to run a query to check and add them to the family tree if yes and create a new family id if no – SunRay Mar 30 '16 at 20:45
  • In the military, family members are always tied to the sponsor SSN. Not all family members are enrolled in Special-Needs - for example, a child with asthma would be, but a healthy brother would not. Families frequently don't know the status of their enrollment, hence the Special-Needs table, which is exported from the program coordinator's (otherwise inaccessible) online database. She knows who is enrolled, but not who is eligible; I know who is eligible, but not if they're enrolled. Thus, many patients get missed if either of us isn't diligent. This project makes it less vulnerable to humans. – pediatrictactic Mar 30 '16 at 21:05
  • The desired end-workflow is this: at a patient's appointment, I notice they have an eligible diagnosis. I look up their name in the database, and if they're not enrolled, I click a button, which sends an email to the program coordinator with the patient's information. She verifies their eligibility and, if eligible, contacts the family for enrollment. The database reflects the change in status. – pediatrictactic Mar 30 '16 at 21:05
  • so far, from the information given, i would think that you need 3 tables. However, I am not entirely sure if i understood your problem. The 3 tables would be A patient table, family relationship table and a special needs table. The patient table would store just your patient details the family relationship table would use the SSN AND family member's ID as primary KEY (i.e a compound key) the special needs table would use the SSN of the sponsor and whichever family member's ID who has the special need – SunRay Mar 30 '16 at 21:20
  • I don't think there is a perfect solution for this; due to the fact that you don't have the sponsor SSN in every row of the Special Needs data, coupled with the fact that last names might not always match, but there is a way it could be done with only one table. Your table would contain your patient list - for ease of use, you could write code to update it from a fresh list periodically. Add a field to store if the patient is enrolled in special needs (false by default). Then have a second VBA script to open the special needs list, and then loop through your patient list. (cont'd) – leowyn Mar 31 '16 at 02:47
  • For each patient, loop through the special needs list looking for someone with the matching last 4 of the SSN. If you find it, then grab the last name, and cycle through the list a second time looking for a match with lastName and the first inital from your patient list. If you find a match, then mark that patient as enrolled in special needs in the table. – leowyn Mar 31 '16 at 02:48
  • Then you can simply go through your patient list, and see if someone was "found" on the special needs list or not, and have a button to compose an email. This won't be 100% reliable (you might miss some, or even have some patients falsely flagged as enrolled) but assuming everyone doesn't have a common last name, it should work well enough. – leowyn Mar 31 '16 at 02:51
  • If you are worried about the chance of errors, you can also, for the second iteration on the Special Needs list, limit it to the 5-10 rows above and below the row where you found the SSN match (assuming they are at least grouped by family) This would reduce the change of falsely marking someone as enrolled, and improve performance too (which could be an issue depending on the size of your lists) – leowyn Mar 31 '16 at 02:59
  • Leowyn's single-table solution and workflow is what I was trying for in the initial description. I don't want/need to retain All-Patients and Special-Needs, just import them to a single table. – pediatrictactic Mar 31 '16 at 12:00
  • I have a feeling the reason why you have issues with storing this dataset is because of normalizing the dataset. Pardon me if i am wrong, i have always faced this issue in the past and i thought it maybe similar to you also. This is a pretty good example http://stackoverflow.com/questions/8437957/difference-between-3nf-and-bcnf-in-simple-terms-must-be-able-to-explain-to-an-8 – SunRay Mar 31 '16 at 14:41

0 Answers0