Updating nonsequential records based on similar records in the same table in Access
<p>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. </p>

<p>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:</p>

<p><em>All-Patients</em>: </p>

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

<p><em>Special-Needs</em>:</p>

<ul>
<li>First Name (string)</li>
<li>Last Name (string)</li>
<li>Middle Initial (string)</li>
<li>Sponsor SSN (only if Role = Sponsor) (string)</li>
<li>Role (Sponsor, Spouse, or Child) (string)</li>
</ul>

<p><strong>CONSTRAINTS:</strong> 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. </p>

<p><strong>Step 1:</strong> 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.</p>

<p><strong>PROBLEM 1:</strong> 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. </p>

<p>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!</p>

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

<pre><code>LastName   FirstName      SSSN      Role
---------------------------------------------
Jones      Mother                   Spouse    
Jones      Daughter                 Child
Jones      Father      555-55-5555  Sponsor
Jones      Son 1                    Child
Jones      Son 2                    Child
</code></pre>

<p>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.</p>

<p>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. </p>

<p>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.</p>

<p><strong>CONCLUSION:</strong> 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).</p>

<p>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!</p>