Combining multiple fields and filtering text

[Guest]

Here is my dilemma. I have a query from a database containing multiple
fields. The initial query searches a total of 8 separate fields for
particular text. For example, the 8 separate fields contain information that
may or may not contain the text I am looking for, but I need to catch the
record in order to report whether that text is included in any of those 8
columns. If I am looking for all cases of "carcinoma" by client from multiple
biopsies that would appear in any of those 8 fields (ie. diagnosis site
1,2,3,etc). Then I need to provide a report that includes the client, patient
tname, and diagnosis. However, when I query, I get all the 8 separate fields
for each patient, and not just the field that contains the information I want
to display. I can add all of the returned fields into one created field, but
I want to filter out the text that doesn't apply to the field with cancer.
How would I go about doing that?

 

[Jeff Boyce]

From your description, your table is well-designed ... if it were a
spreadsheet! Spreadsheets pretty well force you to use "repeating fields"
to handle your multiple biopsies (8 today, but what about tomorrow?!).

Access is a relational database, though -- to get the benefit of the tools
and power of Access, you need to design/model your data relationally.

In your situation, it sounds like you have a patient, with one-to-many
biopsies. In a spreadsheet, you could have 7 empty columns (only one biopsy
so far). In Access, you'd have a Patient table, and a Biopsy table, and
your patient would have ONE row in the biopsy table.

If you keep your current data/table design, you (and Access) will have
headaches doing what you're trying to do. If you redesign your table
structure, you'll find that Access (and you) will have a MUCH easier time.

 

[John Nurick]

The best approach is to normalise your data, replacing the multiple
"diagnosis site" fields with a new table that has one record per
diagnosis site per client. That way, your query only has to search one
field for the text, and only returns data from that one field.

With your existing structure, you may be able to achieve what you want
by using a union query that stacks the multiple "diagnosis site" fields
into a single field, along these lines:

SELECT ClientID, PatientName, DiagnosisSite1 AS DiagnosisSite
UNION
SELECT ClientID, PatientName, DiagnosisSite2 AS DiagnosisSite
UNION
SELECT ClientID, PatientName, DiagnosisSite3 AS DiagnosisSite
....
SELECT ClientID, PatientName, DiagnosisSite8 AS DiagnosisSite;

and then using this query as the basis of your search.

 

[Guest]

Thanks for the feedback!

I agree with you that the design leaves much to be desired (we are currently
designing a new LIS).

The table is actually a SQL table generated from an old AS400 database where
all the diagnoses are listed in one field! So the data is parsed out and
transferred into the SQL database. I have no means to alter this setup...only
to figure out how to work with the data in its existing state.

One patient can have many biopsies over time (performed at different times)
or can have multiple biopsies performed at the same time, representing on
report/record. But you are right, that patient can have many biopsies (more
than 8) at a time.

 

[Guest]

That seems to make sense. I was trying to make it too complicated I think. I
actually got all the diagnoses sites collapsed into one field, but just
wanting to parse out what I wanted, datawise. But I think your idea makes the
most sense. I will give that a try.

Thanks for the help!