Selecting fields in two columns as "OR"

[#1]

I'm sure this has come up before, but searching for it in the forum is hard to do.

I have a table: COHORT_TABLE It contains three fields:

PatientID

DiagCode

MedcinID

I want to be able to select a PatientID based on if they have specific Diagnosis Codes OR MedcinIDs. For exmaple:

PatientID, DiagCode, MedcinID
1, 250.0
2, 250.0, 30479
3, , 30479

I want to be able to select Diagnosis 250.0 and MedcinID 30479 and get Patients 1, 2 and 3. But currently, I can only get patient 2.

I know I can do this with set analysis. I was trying to avoid it. I am considering re-designing the COHORT_TABLE to have only two columns, PatientID and CohortID, and combine DiagCode and MedcinID into that column (with a prefix to identify which code is which). That way, QV's natural "OR" functionality would work, since its all one column. I'm just afraid of confusing my users, if DiagCodes and MedcinIDs are all lumped together in one listbox.

Am I overlooking some option somewhere that makes this easier? I've looked around, but this wouldn't be the first time I've overlooked something obvious (that I just haven't encountered before).

Thanks in advance.

[johnw]

I thought I could make your proposal work by creating list boxes for expressions if(Field='Diag',Code) and if(Field='Medcin',Code). But when I select a value in one, the other box goes gray since they're actually the same field. I can't select a value in both boxes. I can select both values if I just have a straight up list box for Code, but you're right that that would be inconvenient for the users. Hopefully there's a way around it, but it's not jumping out at me. In any case, I'll attach what I have in case someone wants to fiddle with it.

[#3 · Accepted solution]

I ended up going the macro route.

' SetCohort
'
SUB SetCohort
 ActiveDocument.Fields("CohortID").Clear

 set Cohort = ActiveDocument.Fields("CohortID").GetNoValues
 d=0 'Figure on no diagnosis

 'First, Load up Diag Codes
 if ActiveDocument.GetField("CohortDiag").GetValueCount(1) > 0 then
 set Diagnosis = ActiveDocument.fields("DCohortID").GetPossibleValues

 For i = 0 to Diagnosis.Count -1
 Cohort.Add
 Cohort.Item(i).text = Diagnosis.Item(i).text
 Cohort(i).IsNumeric = Diagnosis(i).IsNumeric
 next

 d=Diagnosis.Count
 end if

 'Then, Load up MedcinIDs
 if ActiveDocument.GetField("CohortMedcin").GetValueCount(1) > 0 then
 set Medcin = ActiveDocument.fields("MCohortID").GetPossibleValues

 for i = 0 to Medcin.Count - 1
 Cohort.Add
 Cohort.Item(d+i).text = Medcin.Item(i).text
 Cohort(d+i).IsNumeric = Medcin(i).IsNumeric
 next
 end if

 if Cohort.Count > 0 then
 ActiveDocument.Fields("CohortID").SelectValues Cohort
 end if

END SUB

Basically, CohortID is a field that I've combined both DiagIDs and MedcinIDs (with a prefix to make sure each is unique). CohortDiag and CohortMedcin are the pretty fields I let the user pick, and DCohortID and MCohortID are the grunt-work keys (with prefixes) to match up with CohortID.

The macro is triggered On Select for the pretty fields. Since people pretty much pick them, and leave it while going around the rest of the document, its not like it should be firing all the time.

It looks, almost, as if I took the opposite approach, and yours has the bonus of not using macros. But my users will be less confused if they can pick diags from a listbox of diags, and medcins from a listbox of medcins.

We'll see how it works at run-time with some real user's data on it. I don't if I'll have problems with any slow-downs. I can imagine it will be slow if a lot of items are chosen.

Thanks for your time and your ideas.