searching field

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

Guest

I currently have a form/table with 15 different fields, that can hold similar
information. I know I can search a single field for a specific word/phrase.
Is there a way (with a macro or code) that I can enter the word or phrase I
am looking for...have it search field 1, if not found move on to field 2, and
so on until it has searched all fields and/or found all occurances in the
different fields?

Thank you!
 
Dear 1308:

Nope.

If these 15 columns contain "equivalent" information, such as would
typically be the case if you want to search them all simultaneously for the
same thing, then that indicates your database is built improperly. You
chouls put all 15 fields into one field, but in 15 separate rows. The
search would then be of a single column.

If your current structure is like this:

Key Column
Some Attribute
Text 1
Text 2
Text 3

and so on, up to 15, then you should make two tables from this:

Key Column
Some Attribute

and

Key Column
Sequence
Text

If done this way, searches such as you describe will work well. Not having
"repeating" rows in a table is a rule of good design.

You can make your current setup appear to be this way using a UNION query:

SELECT [Key Column], 1 AS Sequence, [Text 1] AS Text
FROM YourTable
WHERE [Text 1] IS NOT NULL
UNION ALL
SELECT [Key Column], 2 AS Sequence, [Text 2] AS Text
FROM YourTable
WHERE [Text 2] IS NOT NULL
UNION ALL
SELECT [Key Column], 3 AS Sequence, [Text 3] AS Text
FROM YourTable
WHERE [Text 3] IS NOT NULL
UNION ALL
SELECT [Key Column], 4 AS Sequence, [Text 4] AS Text
FROM YourTable
WHERE [Text 4] IS NOT NULL

Repeat this till you have all 15 text values. You can then query the single
column in this to locate all matching strings.

Tom Ellison
 
Tom,

Thank you for your suggestions. I like your idea better...2 separate
tables. A few follow-up questions though...
In your second table, what does sequence refer to?
Also, if I use the 2 table structure and want a single form for data entry,
how do I fill data in on separate rows without going to a new record on the
form? If I have 2 fields going to the same source once I type something in
one it shows up in the other.

Thank you!

Tom Ellison said:
Dear 1308:

Nope.

If these 15 columns contain "equivalent" information, such as would
typically be the case if you want to search them all simultaneously for the
same thing, then that indicates your database is built improperly. You
chouls put all 15 fields into one field, but in 15 separate rows. The
search would then be of a single column.

If your current structure is like this:

Key Column
Some Attribute
Text 1
Text 2
Text 3

and so on, up to 15, then you should make two tables from this:

Key Column
Some Attribute

and

Key Column
Sequence
Text

If done this way, searches such as you describe will work well. Not having
"repeating" rows in a table is a rule of good design.

You can make your current setup appear to be this way using a UNION query:

SELECT [Key Column], 1 AS Sequence, [Text 1] AS Text
FROM YourTable
WHERE [Text 1] IS NOT NULL
UNION ALL
SELECT [Key Column], 2 AS Sequence, [Text 2] AS Text
FROM YourTable
WHERE [Text 2] IS NOT NULL
UNION ALL
SELECT [Key Column], 3 AS Sequence, [Text 3] AS Text
FROM YourTable
WHERE [Text 3] IS NOT NULL
UNION ALL
SELECT [Key Column], 4 AS Sequence, [Text 4] AS Text
FROM YourTable
WHERE [Text 4] IS NOT NULL

Repeat this till you have all 15 text values. You can then query the single
column in this to locate all matching strings.

Tom Ellison


accessuser1308 said:
I currently have a form/table with 15 different fields, that can hold
similar
information. I know I can search a single field for a specific
word/phrase.
Is there a way (with a macro or code) that I can enter the word or phrase
I
am looking for...have it search field 1, if not found move on to field 2,
and
so on until it has searched all fields and/or found all occurances in the
different fields?

Thank you!
Click to expand...
Click to expand...
 
Dear 1308:

The sequence preserves the order of the previous 15 columns. When I perform
something like this, I always at least consider whether there is any
information in that sequence worth preserving. There often is.

You must go to a new record on the form. Minimally, this could be built
with a single column in a continuous subform. If a user wants to enter
several Text values, he may enter one and press tab to move down to the next
one, instead of moving across to the next one. That's not so bad, eh?

I don't know what construction you are considering that shows things twice.
I would likely avoid anything like that.

Tom Ellison


accessuser1308 said:
Tom,

Thank you for your suggestions. I like your idea better...2 separate
tables. A few follow-up questions though...
In your second table, what does sequence refer to?
Also, if I use the 2 table structure and want a single form for data
entry,
how do I fill data in on separate rows without going to a new record on
the
form? If I have 2 fields going to the same source once I type something
in
one it shows up in the other.

Thank you!

Tom Ellison said:
Dear 1308:

Nope.

If these 15 columns contain "equivalent" information, such as would
typically be the case if you want to search them all simultaneously for
the
same thing, then that indicates your database is built improperly. You
chouls put all 15 fields into one field, but in 15 separate rows. The
search would then be of a single column.

If your current structure is like this:

Key Column
Some Attribute
Text 1
Text 2
Text 3

and so on, up to 15, then you should make two tables from this:

Key Column
Some Attribute

and

Key Column
Sequence
Text

If done this way, searches such as you describe will work well. Not
having
"repeating" rows in a table is a rule of good design.

You can make your current setup appear to be this way using a UNION
query:

SELECT [Key Column], 1 AS Sequence, [Text 1] AS Text
FROM YourTable
WHERE [Text 1] IS NOT NULL
UNION ALL
SELECT [Key Column], 2 AS Sequence, [Text 2] AS Text
FROM YourTable
WHERE [Text 2] IS NOT NULL
UNION ALL
SELECT [Key Column], 3 AS Sequence, [Text 3] AS Text
FROM YourTable
WHERE [Text 3] IS NOT NULL
UNION ALL
SELECT [Key Column], 4 AS Sequence, [Text 4] AS Text
FROM YourTable
WHERE [Text 4] IS NOT NULL

Repeat this till you have all 15 text values. You can then query the
single
column in this to locate all matching strings.

Tom Ellison


message
I currently have a form/table with 15 different fields, that can hold
similar
information. I know I can search a single field for a specific
word/phrase.
Is there a way (with a macro or code) that I can enter the word or
phrase
I
am looking for...have it search field 1, if not found move on to field
2,
and
so on until it has searched all fields and/or found all occurances in
the
different fields?

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top