Query of multiple fields containing similar data

[sueshe]

I have a Table that contains 10 diagnosis fields, each containing up to 10
diagnosis codes for each patient in the table. I need to pull out only those
patients that contain a V code (meaning they start with a V) in any of the 10
diagnosis fields. I have tried doing a v* for Criteria in each diagnosis
field, but it comes up with no records each time I run it(there are records
that contain V codes. If the patient does have a V code in any of the 10
fields, I need it to show any codes that are on that record along with the V
code.

 

[Allen Browne]

It is possible to use:
Like "V*"
under every one of those fields, each on a *different* row of the Criteria
block of your query. You might run out of rows in the query design grid
though.

The real solution is to create a related table, with just one column where
you enter these codes. You can enter as many rows as you need (one to many
relationship.) It is then dead simple to create a query containing the 2
tables, and you just need the Criteria under the one field.

Whenever you see repeating columns like that, the related table is always
the solution.