Query over multiple columns

[aerabinovich]

I have a patient database with five columns that say procedure and the Procedure tables have the same fields. I need to query a specific procedure. How do I make a query that will ultimately filter any patient with a specific procedure, but the procedure is not necessarily listed as the primary one? EX: Proc1, Proc2, Proc3, Proc4, Proc5 and I need to query a procedure coded 18 in any of these columns.

Or maybe a query via the SQL view that will ultimately say search "1 or 2 or 15 or 17 or 18 or 30" in Proc1, if no match in Proc1 search Proc2, if not in Proc2 check Proc3, etc.

[jzwp11]

Welcome to the forum!

First and most importantly, you have a structural problem with your table that needs to be corrected.

If a patient can have many procedures, that is a one-to-many relationship. By the rules of normalization, those procedures should be records (not fields) in a related table. Furthermore, I would have to assume that a procedure can be performed on many patients--another one-to-many relationship. When you have two one-to-many relationships between the same to entities (patients and procedures) you have a many-to-many relationship. To properly capture the data, you will need 3 tables as follows:

tblPatient
-pkPatientID primary key, autonumber
-txtFName text field to hold patient's first name
-txtLName text field to hold patient's last name
other fields related to the patient

tblProcedures (a table to hold a list of all procedures, each procedure would be a record in this table)
-pkProcID primary key, autonumber
-txtProcedureName text field to hold the name of the procedure

Now a table to hold the many-to-many relationship (also called a junction table).

tblPatientProcedures
-pkPatientProcID primary key, autonumber
-fkPatientID foreign key to tblPatients (this must be a long number integer datatype field)
-fkProcID foreign key to tblProcedures (this must also be a long number integer datatype field)
-dteProcedure (date field to hold when the procedure was conducted)

With the structure above, the querying of a specific procedure is easy. In the query window you would join the tblPatientProcedures table with the tblProcedures via the pkProcID--fkProcID relationship and then put a criteria in the query to get the one you want.