Show matching data in a form as separate sets

[akaRose]

I'm working on a database that will hold bacterial strain typing information.

I want to open a form, select from a drop-down the test method, say test1, test2, or test1 AND test2 to look for patients (bacterial strains) that have identical matching results. Test1 is a 12 digit code, and test 2 is a 15 digit code. I would like to show the groups of matching strains separate from each other and then assign a group ID.

I've been working away at this and have found something that sort of works. The code below will show a list of records that have matching test results for the 2 tests (MIRU12 and OctalCode). However I would like to show the matches set by set as I may have 100's of matches and need to identify each matching set separately. Does anyone know of a way to do this?

Thanks

Private Sub cmdSearch_Click()
Me.lstResult.RowSource = "SELECT ENTRYTABLE.KEY, ENTRYTABLE.FIRSTNAME, ENTRYTABLE.SURNAME, ENTRYTABLE.MIRU12, ENTRYTABLE.OCTALCODE, ENTRYTABLE.REGION_DESIGNATION, ENTRYTABLE.STRAIN FROM ENTRYTABLE WHERE (((ENTRYTABLE.OctalCode) In (SELECT [OctalCode] FROM [ENTRYTABLE] As Tmp GROUP BY [OctalCode],[MIRU12] HAVING Count(*)>1 And [MIRU12] = [ENTRYTABLE].[MIRU12])))ORDER BY ENTRYTABLE.MIRU12"
End Sub

 

[lespaul]

would probably be easier to help you if you would give some example data from ENTRYTABLE and the expected results....




Leslie

Have you met Hardy Heron?

 

[akaRose]

Here is an example of the type of information I need to display. The two tests are MIRU_12 and OctalCode. For a strain to be a match these two tests have to have the EXACT same digital code. So A and B have the same strain, and C,D and E are the same. But in my database I may have 100 sets of matching strains each week or month and would like to be able to display the matches on a case by case basis, and confirm that they're a match and that its not the same patient (unfortunately the integrity of the data is a bit of a problem due to patients giving different information each time they are tested) so this needs to be checked manually before a positive match is reported. Right now I just see a list as in the example below and can't separate it out and then press a button to confirm match and move to the next set. Hope this makes sense!

Thanks for any help

Key Patient HealthCard No. Case No. MIRU_12 OctalCode
001/9999 Mr. A 123456789 111 223615123425 777773477611773
002/9999 Mr. B 234567890 619 223615123425 777773477611773
121/9999 Mr. C 345678901 235 116123452234 777776617734627
114/9999 Mr. D 456789012 564 116123452234 777776617734627
116/9999 Mr. E 567890123 658 116123452234 777776617734627

 

[lespaul]

No sorry, I still don't understand....this is the data in the table....you have some kind of strain that you test for and patients you are testing. You want to display matches of some kind based on OctalCode and MIRU_12 matching between the records and that if they do match they are different patients? You can see the list you provided but can't separate something out from it. there is only one of each patient listed so I can't tell what you want to do about matching patients before confirming a match.

If you would show an example of the table with records that should be included and excluded from your results and a reason WHY those records should be included or excluded, I'm sure I can help, it doesn't sound like a difficult query, I just am not getting your requirements.

Leslie

 

[akaRose]

Sorry I'm not explaining this well. We get hundreds of patient specimens (for example a sputum sample) and test this for a bacteria(tuberculosis). Then report back to the doctor or hospital that this patient is positive for that bacteria. But we also take the testing a step further for the public health units and do strain typing to determine who has transmitted TB to whom (most often its straight-forward and is within families but not always), or possibly if there's an outbreak - we can tell b/c a whole bunch of people will have the same strain (based on the digital code - this is generated based on the DNA of the bacteria...not to get into too many details). Anyways my form currently uses the select query that I showed before and will display all people that were diagnosed with TB that had matching strains (meaning its highly likely it was passed from one person to the other).

The first step that I would like to figure out is how to show the results of each matching set separately rather than as one big long list which is what it currently does. Once I can see the matching sets separately I can also confirm if these are indeed multiple patients carrying the same strain of TB, or they may be a match simply because they're the same person. We have many immigrant and homeless people as part of our population that get sick with TB - unfortunately this means that they may interchangably give their first name as their last, a slight variation of their name, etc etc. So I still need to comfirm that its a unique match and alert public health that we have transmission of the bacteria. They obviously don't want me bugging them with alerts and then saying oops of course their strain matches its the same person (they get tested multiple times over the course of treatment - but generally do not acquire a new strain of the bacteria). Anyways I need to confirm and asign a cluster ID number as well.

Sorry this all seems rather complicated to explain. I'll give more examples of the kind of output I currently have. For obvious reasons I can't actually include real patient information.

Thanks again.
Key Patient HealthCard No. Case No. MIRU_12 OctalCode
001/9999 Abe Smith 123456789 111 223615123425 777773477611773
002/9999 Smith Abe 234567890 619 223615123425 777773477611773
121/9999 Carol Chistmas 345678901 235 116123452234 777776617734627
114/9999 John Doe 456789012 564 116123452234 777776617734627
116/9999 Joan Arc 567890123 658 116123452234 777776617734627
190/9999 Iris Flower 678901234 764 116123452234 777776617734622
632/9999 Rose Flower 678901234 764 116123452234 777776617734622

If this were the list that was displayed, the first two are a match but are actually the same person so I don't want to assign a cluster ID or confirm as a strain match/transmission match. Carol, John and Joan have strains that match and I would want to assign a cluster ID and alert public health. Iris and Rose also match (likely family members) but need to assign a cluster ID and inform public health.

Forgot to talk about the table. I have one table containing the 3000+ patients over the last ~6 years that have been tested for TB and are positive. On a weekly basis new patients are added and their TB strain information is also input. So when I do the query its looking at the whole table and comparing all entries against one another.

 

[lespaul]

So to identify those that are matches do this:

SELECT A.KEY, A.FIRSTNAME, A.SURNAME, A.MIRU12, A.OCTALCODE, A.REGION_DESIGNATION, A.STRAIN 
FROM EntryTable A
INNER JOIN YourTableName B ON A.MIRU12 = B.MIRU12 AND A.OctalCode = B.OctalCode

that will return only records that match some other record.

Now it seems like the next step in the process is more of a display issue....you only want to display the matches for whichever MIRU_12 and OctalCode you are working with, right? In that case you can set up a form with a subform.

I would create another query that gets the MIRU_12 and OctalCodes your first query returns and use that as the source for a form and the first query with the details as the source of the subform. And then you can just go through and see if you need to assign a cluster id.

So your first query above would be qryDetails and then:

SELECT Distinct MIRU_12, OctalCode FROM qryDetails

then your form would be something like:
[tt]
MIRU_12:_______________
OctalCode:_____________

__________________________________________________________
Key Patient HealthCard CaseNo



__________________________________________________________
[/tt]

and for each set of codes you display the details.

Would that accomplish what you need?

Leslie

Have you met Hardy Heron?

 

[akaRose]

Thank you, thank you, thank you.

I'm able to get it working. I still have some stuff to do, but this has gotten me on my way.

If I may ask another question? If I would like to display a total number of strain matches for each distinct set, I'm assuming I would put in a count function - but I'm not sure how or where exactly. It would be counting the Entrytable.key for each distinct group of matches.

Thanks again!!

 

[lespaul]

Something like:

SELECT MIRU_12, OctalCode, COUNT(*) FROM EntryTable
GROUP BY MIRU_12, OctalCode

if you only want to find ones that have more than one entry you would do:

SELECT MIRU_12, OctalCode, COUNT(*) FROM EntryTable
GROUP BY MIRU_12, OctalCode
HAVING Count(*) > 1

Glad to help!

Leslie

 

[akaRose]

Got it working perfectly thanks again for all your help : )

 

[lespaul]

you're welcome!

Leslie