Filter on same fields with multiple combos

[Guest]

Is there a simple way to match all values in multiple combo boxes against
four different fields in a table to filter records in a table?

I have a table with patient visit information (tblVisits). There are four
fields in the table, (Diagnosis1, Diagnosis2, Diagnosis3, and Diagnosis4). I
also have a 'search' form (frmSearchMultiDiag) with four combos on it
(cboDiagCodeSelect1, cboDiagCodeSelect2, etc.)

What I want to do is have the user to be able to enter up to four diagnosis
codes on the form, and then have the recordset filtered to display all the
record(s) that contain those diagnoses in any of the four diagnosis fields.
My problem seems to be that I can only manage to filter the records in the
exact order as the data appears in my combos, meaning that for whatever I
select in cboDiagCodeSelect1 must appear in the Diagnosis1 field, the
cboDiagCodeSelect2 must be in the Diagnosis2 field, etc. But I need a way to
search through all four of the Diagnosis fields for all the entries in the
combos as two records could have the same diagnosis code but in different
fields. For example, Patient 'A' could have 17.2 as Diagnosis1 and 29.2 as
Diagnosis2, while Patient 'B' has 29.2 as Diagnosis1 and 17.2 as Diagnosis2,
depending on what their primary diagnosis is. Also, some of the records only
have one or two diagnoses, so I need a way to include the nulls.

Basically, I need something like: Find the record(s) in the table where
cboDiagCodeSelect1 = Diagnosis1 or Diagnosis2 or Diagnosis3 or Diagnosis 4
AND
cboDiagCodeSelect2 = Diagnosis1 or Diagnosis2 or Diagnosis3 or Diagnosis4 or
null AND cboDiagCodeSelect3 = Diagnosis1 or 2 or 3 or 4 or null AND
cboDiagCodeSelect4 = Diagnosis1 or 2 or 3 or 4 or null.

I'm new to Access and I've tried to do this using a parameter query and the
query builder but without any success, but I'm thinking that there must be a
way to do this using VBA code attached to the form. Any suggestions?

 

[Marshall Barton]

Is there a simple way to match all values in multiple combo boxes against
four different fields in a table to filter records in a table?

I have a table with patient visit information (tblVisits). There are four
fields in the table, (Diagnosis1, Diagnosis2, Diagnosis3, and Diagnosis4). I
also have a 'search' form (frmSearchMultiDiag) with four combos on it
(cboDiagCodeSelect1, cboDiagCodeSelect2, etc.)

What I want to do is have the user to be able to enter up to four diagnosis
codes on the form, and then have the recordset filtered to display all the
record(s) that contain those diagnoses in any of the four diagnosis fields.
My problem seems to be that I can only manage to filter the records in the
exact order as the data appears in my combos, meaning that for whatever I
select in cboDiagCodeSelect1 must appear in the Diagnosis1 field, the
cboDiagCodeSelect2 must be in the Diagnosis2 field, etc. But I need a way to
search through all four of the Diagnosis fields for all the entries in the
combos as two records could have the same diagnosis code but in different
fields. For example, Patient 'A' could have 17.2 as Diagnosis1 and 29.2 as
Diagnosis2, while Patient 'B' has 29.2 as Diagnosis1 and 17.2 as Diagnosis2,
depending on what their primary diagnosis is. Also, some of the records only
have one or two diagnoses, so I need a way to include the nulls.

Basically, I need something like: Find the record(s) in the table where
cboDiagCodeSelect1 = Diagnosis1 or Diagnosis2 or Diagnosis3 or Diagnosis 4
AND
cboDiagCodeSelect2 = Diagnosis1 or Diagnosis2 or Diagnosis3 or Diagnosis4 or
null AND cboDiagCodeSelect3 = Diagnosis1 or 2 or 3 or 4 or null AND
cboDiagCodeSelect4 = Diagnosis1 or 2 or 3 or 4 or null.

I'm new to Access and I've tried to do this using a parameter query and the
query builder but without any success, but I'm thinking that there must be a
way to do this using VBA code attached to the form. Any suggestions?

Before you dig that hole any deeper, take a timeout and read
up on Database Normalization. A quick Google will get you
more information on the subject than you could ever need, so
pick a couple that cover the first 3 normal forms in terms
you can understand without breaking out in a cold sweat ;-)

When you understand enough to create a diagnosis table so
you can get rid of those repeating fields, the answer to
your question will be pretty easy.

As your table is now, I can't say it is impossible, but I
can say for sure that it will at least be a mess.

 

[Guest]

:

Before you dig that hole any deeper, take a timeout and read
up on Database Normalization. A quick Google will get you
more information on the subject than you could ever need, so
pick a couple that cover the first 3 normal forms in terms
you can understand without breaking out in a cold sweat ;-)

When you understand enough to create a diagnosis table so
you can get rid of those repeating fields, the answer to
your question will be pretty easy.

As your table is now, I can't say it is impossible, but I
can say for sure that it will at least be a mess.

Thanks. I've had a rumble through some sites with normalization info on
it. I believe I'll have to create a new table and call it 'VisitDetails' to
detail the specifics of each patient's visit using the Visit Number as the
key field, similar to what the Northwind DB does for their Order Details
table. But again, thanks for your help. I think you've saved me a lot of
future hassle.