Find something in any field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

If I have a table that has many fields I want to find a value in, how do I
write the Select statement?
There are about 42 identical fields. So I want to say something like

SELECT Chartname FROM Org2
WHERE Field1 LIKE "*Tom Stevens*" OR Field2 LIKE "*Tom Stevens*" OR Field3 LIKE
"*Tom Stevens*"

With 42 identical fields, F1, F2, F3, F4, F5..., how do I construct the
statement without it being enormous and tedious?






(e-mail address removed)
 
Dear Spam:

The reason this is difficult is that databases aren't made to work
that way. A database works around certain principles that generally
exclude the construction of a table with 42 identical columns.

Probably, this is constructed to allow from 1 to 42 values to be
entered. The way this works in databases is to use 1 to 42, or more,
rows to store these one per row, possibly using another table. Using
whatever primary key these rows already use as a beginning point for
that table, add a column to contain the number 1 to 42 (or more) and a
single column for these names.

You can actually insert these into such a table using a UNION ALL
query that give that primary key value, a constant from 1 to 42, and
the value from the associated column:

SELECT PrimaryKeyColumn, 1 AS NameNumber, F1 AS Name
FROM YourTable
WHERE F1 IS NOT NULL
UNION ALL
SELECT PrimaryKeyColumn, 2 AS NameNumber, F2 AS Name
FROM YourTable
WHERE F2 IS NOT NULL
UNION ALL
SELECT PrimaryKeyColumn, 3 AS NameNumber, F3 AS Name
FROM YourTable
WHERE F3 IS NOT NULL

Keep going till you have all 42. You can even perform the search you
want on the results of the above union, though this is not so
efficient in the long run.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I have a form with texboxes 8 across and 8 down. I fill some with Text then
save them down to F1, F2, F3, etc, and a field for a Name. Thus, when I pull up
that name from a combo box, I pull up fields 1-42 into controls 1-42, making
anything blank invisible.

It works fine. Its searching for things that is difficult.



(e-mail address removed)
 
Dear Spam:

One of the things about this that is not fine is your searching
problem. It is bad design, based on the fact that databases are not
efficient when used this way. I've tried to point you to the way it
can be improved. I don't know what else to do.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top