Query excluding certain matches (tricker than it sounds)

[Sephaerius]

For some brief background, I'm working with a hospital system on matching up accounts by patient names - what's happening here, is that a patient visits a hospital on a outpatient basis, then within 3 days visits again and is admitted as an inpatient. Those two accounts need to be matched. This can happen within the same hospital, or between two hospitals in town. Say, a patient visits the ER down the street, then, within 3 days, is admitted to a different hospital.



I've built a database that pulls in these reports and parses them out as I described above. That wasn't hard. Here's where I'm stuck: in one segment there are 3 hospitals that return matches, but not all combinations are valid.
For instance,
* finding two or more results within hospital A is valid (A+A),
* finding two or more results within hospital B is valid (B+B),
* finding two or more results within hospital C is valid (C+C),
* finding two or more results between A and C are valid (A+C or C+A),

**BUT finding two or more results between A and B, or B and C are NOT valid.

My current query will capture everything above, including the invalid A+B, B+C combinations. I need a function to seek and destroy records where the patient name matches, but the hospital combo is A+B, B+A, B+C, and C+B.

I've considered running a delete query on my final results table, to prune out the unwanted records, but I'm not sure how to write that syntax.

Any guidance here would be appreciated!
Thanks,

[rpeare]

I would approach this problem with code rather than a series of queries, particularly if this is just an attempt to get a list of unique clients across all three sources. I would be cautious about using just the patient name though, maybe the first three characters of the first and last name plus the date of birth might be a better match, particularly if there's no social security number or other unique identifier across all three systems. Can you describe what you're currently doing a bit better, are you accumulating all records into a single table with a field indicating the facility? Have you got a sample database you could provide with some garbage data in it that would duplicate what you're talking about (rather than me having to recreate something).

[Sephaerius]

I would approach this problem with code rather than a series of queries, particularly if this is just an attempt to get a list of unique clients across all three sources. I would be cautious about using just the patient name though, maybe the first three characters of the first and last name plus the date of birth might be a better match, particularly if there's no social security number or other unique identifier across all three systems. Can you describe what you're currently doing a bit better, are you accumulating all records into a single table with a field indicating the facility? Have you got a sample database you could provide with some garbage data in it that would duplicate what you're talking about (rather than me having to recreate something).

Here's what I'm doing:
My data source is a comma delimited, .csv report, which I am importing into an Access table. I use a lookup table to match patient types to either inpatient or outpatient. Then use a query to separate inpatient accounts onto one table, and outpatient accounts onto another table. I use a query to compare those two tables, where patient name matches, and the outpatient discharge date is within 3 days of the matching inpatient admission date. This works flawlessly, and the patient names will always match because the source report is coming out of one single patient accounting system, so that's not a concern.

The different hospital facilities are indicated by an alpha prefix in one of the columns. In my example above I called them A, B, and C.

Now, the two exceptions I need to make with that query is to prevent A+B and B+C hospital matches, allowing all other matches. I've tried adding Criteria to the query under Alpha for <> "B" on both the inpatient and outpatient tables, however the problem is that this also eliminates B+B matches, which are valid and need to be included.

I'm open to code as well, if you think VBA would be a good option?

I'm hesitant to share a sample database publicly given the nature of healthcare privacy laws, and with respect to my client. I just can't be sure I'd be able to scrub it of all identifying information.

Thanks,

[rpeare]

That's why I suggested a sample set of data with garbage in it, just enough records to simulate what you're after. I also work in the HIPAA related field :P

alternately you could create a spreadsheet with some junk data in it that had just the relevant fields in it that could be imported easily into an access table. Without knowing your data structure or having an example of your data it would be pretty hard to direct you other than in the most general terms.

Without any of that I would:
Cycle through unique clients
Count the records for each location (A B and C)
if the count in A was greater than zero and the count in B was greater than zero, delete the a records in A
if the count in C was greater than zero and the count in B was greater than zero, delete the records in C

Though I think I might be more inclined to have an indicator field rather than physically deleting the record something like a yes/no field so if you need to reference any of the non relevant records you still have them available.