locating same text in multiple fields

[Mike Vet]

I have a drug database that has 8 fields for indications. I need to search
for the same indication in all 8 fields. Example: Search for "inflammation"
in all 8 fields, and return the drug if "inflammation" is found IN ANY ONE of
the 8 fields. I write "inflammation" in the Criteria row of field 1, and
"inflammation" in the OR row of the next 7 fields. The problem I am having
is that if "inflammation" is not in the first field, the OR statements will
not look for it in the other 7 fields.

 

[Jeff Boyce]

Mike

If you have "8 fields for indications", you have ... a spreadsheet! In a
well-normalized relational database, you'd only need to search a single
field.

You can keep trying to come up with workarounds for a data design for which
Access is NOT optimized.

Or you can normalize your data structure and take advantage of Access'
relationally-oriented features/functions.

Pay now or pay later!<g>

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[vanderghast]

I agree with Jeff.

But while you make the modifications, you can patch this particular test
with something like:



SELECT *
FROM table
WHERE "inflammation" IN( indication1, indication2, indication3, indication4,
indication5, indication6, indication7, indication8)




Generally, we have: fieldName IN( list of constants ), but here, it is
just the reverse: constant IN( list of fields )





But again, you should rather build on a normalized table:

Instead of:

someID, someRef, indic1, indic2, ..., indic8 ' fields
1010, 2020, "aaa", "bb", ... , null ' example of data

Try:

someID, someRef, indication ' fields
1010 2020 "aaa"
1010 2020 "bb"
....

Note that it is then easy to find what you look for:

SELECT someID, someRef
FROM normalTable
WHERE indication = "inflamation"


And if, I agree, the whole data may be harder to read, for a human, you can
still make a crosstab query to present the data to a human reader (rather
than presenting the raw normalized table). Access is also great in that you
can have DATA in TABLE and CONSULTATION through FORM. You don't have to
built your table for consultation. Instead, built your table for easy work
by the computer, and built your form for easy work for a human. Divide to
conquer! You can get each goal (computer work vs user interraction)
independantly... well, almost independantly.



Vanderghast, Access MVP