Search many fields to find at least one that meets my criteria.

[Guest]

I have a list of fields of which I need to searched for one criteria. I only
need at least one fields to match the criteria, not all of them. How should i
set up this querie, aside from setting up a querie for each field one at a
time to see if it matches?

 

[Duane Hookom]

This sounds a bit un-normalized but I could be wrong. You can use a value
from a control on a form against more than one field in a query:

SELECT tblA.*
FROM tblA
WHERE FieldA = Forms!frmA!txtB or FieldB = Forms!frmA!txtB or FieldC =
Forms!frmA!txtB or FieldD = Forms!frmA!txtB;

 

[Guest]

Thanks Duane for responding to my question. I do not understand how to apply
what you have suggested. I am storing experimental infomation which can have
up to 30 samples in the one run of the experiment, each of which are in
different fields. I am trying to search for an experiment that uses the
sample I am at that moment interested in. I do not care what the other
samples where in that experiment as long I can find the experiments that
involved my samples.

 

[Duane Hookom]

Can you share more about your data? What are typical values in your table
and typical searches.

If you are storing 30 samples as 30 fields then you have an un-normalized
table structure. 30 samples should create 30 records. This would allow you
to easily create a search query since you would be searching only one field,
not 30.

 

[Guest]

My typical values are codes for the samples eg. 5023 or 2064a.The reason I
have each sample in a different field is that it is important that the order
of samples used in the experiments is maintained for future reference. So I
would like to search to find experiments using 5023 say, no matter what place
it was in, in the experiment, it does not matter if it was the first sample
or the fifth. However I need to know what place it was in so that I can refer
to the results of the experiment for that particular sample.

I am unsure what an un-normalized table structure means, and if I can avoid
this by restructuring my table?

Thanks for your help toot.

 

[Duane Hookom]

Jeff Conrad has information on table design and normalization at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101.

You could try to keep your structure but search against a union query based
on your "spreadsheet" type table.

SELECT ID, 1 as Code, [Code1] as SampleValue
FROM tblNoNameGiven
UNION ALL
SELECT ID, 2, [Code2]
FROM tblNoNameGiven
UNION ALL
SELECT ID, 3, [Code3]
FROM tblNoNameGiven
UNION ALL
SELECT ID, 4, [Code4]
FROM tblNoNameGiven
UNION ALL
--- etc ----
FROM tblNoNameGiven;

This would allow you to search the SampleValue field.