How Do I Get A Query to search all phone number fields in a table

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

Guest

I need to Search all Phone Numbers Fields (3) in my customers table and list
them all in a combo box on my form so I Can Find any number in my database ...
 
Dear JJ:

Two suggestions:

Write 3 separate queries to retrieve 3 lists of phone numbers, and use a
UNION.

Redesign your tables so this is better normalized.

Tom Ellison
 
I need to Search all Phone Numbers Fields (3) in my customers table and list
them all in a combo box on my form so I Can Find any number in my database ...
Click to expand...

Use a UNION query:

SELECT [Phone] FROM [Customers] WHERE [Phone] IS NOT NULL
UNION
SELECT [CellPhone] FROM [Customers] WHERE [CellPhone] IS NOT NULL
UNION
SELECT [Fax] FROM [Customers] WHERE [Fax] IS NOT NULL
ORDER BY [Phone];

This won't help you FIND numbers in the database but it will list them
all in a combo box.

John W. Vinson[MVP]
 
Tom and John mentioned Union queries to return all the phone numbers in a
combo box. This should work fine unless you have a lot of records. Two
reasons:

1. Union queries are inefficient as they must look for duplicates and remove
them from the return. UNION ALL is faster; however, you'll get any
duplicates. Actually a UNION ALL may work well for phone numbers, unless
someone uses the same number in more than one category (say both Home and
Fax) or more than one person shares the same phone number.

2. Combo boxes can get very sluggish when over a few thousand records in
them. In that case I use some other method of searching for the data.
 
Very true, Jerry.

If you don't want the UNION to remove duplicates, use UNION ALL and it will
be faster. If you do want to remove duplicates, then it will take a while.
This is as you have stated. However, any other method of producing the list
will have that penalty when it is needed, so there's not much of an argument
there.

Beyond what you said, the combo box has an upper limit of 65K rows that can
be very troublesome. I have a subform design that can substitute, and even
pops up. It only retrieves as many rows at one time as are displayed, and
has no upper limit. One key to this is using a vertical scroll bar control,
that changes the subform's RecordSource to be the TOP N needed to fill the
list needed at any moment. The OnChange event allows the search to automate
the change of RecordSource as a user types, so all the behavior of the combo
box is emulated without any of the performance or other limtations.

That was several days labor to create. Some day I may need to produce a
wizzard that does this automatically and publish the whole thing. Sigh.

I like your "Pick two." motto. I am a former bicycle repairman and racer
(college days). It's widely applicable.

Tom Ellison
 
My daughter is a Cat 2 and races for MSU in Texas. I stopped racing about 4
years ago after a very bad crash (and that was after having a heart attack in
2000 while training!).

Back them my motto was "Last is a place too".
 
Dear Jerry:

My crash was when a spectator walked across the course. We were racing
around campus in Lincoln, NE in a 40 mile race, back in 1971. Now I've
dated myself!

I wasn't hurt in the crash, but it was about an hour into the race. I did
cramp up, but stomped my legs and got back on. But I started to drop behind
the leaders. It was a short course, and I was still on the lead lap at the
time, within a quarter mile of the leaders anyway.

After the race I found the problem. The crash had bent my brake, and the
pad had worn off completely. I think something like that would probably
slow you down!

It's fun to remember and relate this. Nice to meet you, and find a common
interest other than Access.

Tom Ellison
 
Back
Top