By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,759 Members | 1,778 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,759 IT Pros & Developers. It's quick & easy.

How do I search for the same criteria in three or more fields in one table?

P: n/a
I am trying to create a query that will show me who is phoning who in an
organisation from available Telephone Billing information. I am creating a
MSAccess 2000 database with a few few tables, two of which are:

TableMembers: (containg fields Refs, DateCreated, MembershipNo,
OfficeLocation ...NB: Refs has a Primary Key - No Duplicates)
TablePeople: (containing fields: Refs, Name, Addr, TelHome, TelWork,
TelMobile & TelFax)
TableTelBills: (containing fields: Refs, TelNo, DateCalled, Duration,
TelType)

I am trying to create a query that will use a simple searching criteria eg.,

Like "*" [Enter the Tel No or part Tel No to search:] & "*"

to search all the Tel fields in the TablePeople and TableTelBills (TelHome,
TelWork, TelMobile, TelFax and TelNo) but am running in difficulties.

I start by creating a query and adding the tables TablePeople and
TableTelBills and TableMembers.
I use the Refs from the Table Members as a base criteria but do not know how
to create criteria that will search all Tel fields at once!

I would appreciate any and all help people!

Jan
Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Jan Szymczuk (sz******@btinternet.com) writes:
I am trying to create a query that will show me who is phoning who in an
organisation from available Telephone Billing information. I am creating a
MSAccess 2000 database with a few few tables, two of which are:

TableMembers: (containg fields Refs, DateCreated, MembershipNo,
OfficeLocation ...NB: Refs has a Primary Key - No Duplicates)
TablePeople: (containing fields: Refs, Name, Addr, TelHome, TelWork,
TelMobile & TelFax)
TableTelBills: (containing fields: Refs, TelNo, DateCalled, Duration,
TelType)

I am trying to create a query that will use a simple searching criteria
eg.,

Like "*" [Enter the Tel No or part Tel No to search:] & "*"

to search all the Tel fields in the TablePeople and TableTelBills
(TelHome, TelWork, TelMobile, TelFax and TelNo) but am running in
difficulties.

I start by creating a query and adding the tables TablePeople and
TableTelBills and TableMembers. I use the Refs from the Table Members as
a base criteria but do not know how to create criteria that will search
all Tel fields at once!

I would appreciate any and all help people!


The one help I can give is try a forum devoted to Access. This is a
newsgroup for SQL Server, and what applies to SQL Server may not
apply to Access and vice versa.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

P: n/a
"Jan Szymczuk" <sz******@btinternet.com> wrote in
news:cs**********@sparta.btinternet.com:
I use the Refs from the Table Members as a base criteria but do not
know how to create criteria that will search all Tel fields at once!

You can use one of these two kinds of expressions:

WHERE Tel1 LIKE "*" & [Enter the Tel No or part Tel No to search:] & "*"
OR Tel2 LIKE "*" & [Enter the Tel No or part Tel No to search:] & "*"
OR Tel3 LIKE "*" & [Enter the Tel No or part Tel No to search:] & "*"

or you can use

WHERE Tel1 & Tel2 & Tel3 LIKE "*" & [Enter the Tel No or part Tel No to
search:] & "*"

Erland Sommarskog wrote:
The one help I can give is try a forum devoted to Access. This is a
newsgroup for SQL Server, and what applies to SQL Server may not
apply to Access and vice versa.


Erland, just so you know, the bracketed expression represents a kind of
parameter for the query. Basically, at run time, any bracketed expression
that Access doesn't recognize, will cause a messagebox to prompt for a
value.
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.