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

[Jan Szymczuk]

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

[Darryl Kerkeslager]

"Jan Szymczuk" <sz******@btinternet.com> wrote

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!

Create a table, phone, with fields:

phone_id ' primary key, autonumber, Long
phone_number ' text 10 - store only digits, not ()-
phone_type_id ' Long, foreign key to a table of phone types
(home, cell, employer, etc)
phone_member_id ' Long, foreign key to member table (or person table -
not sure what the distinction is)

You can now search for the entire telephone list with one query.
Darryl Kerkeslager

[Jan Szymczuk]

Dear Darryl Kerkeslager

Thanks for your solution, but I already know about placing all the TelNo
fields in one table and running one query to find any particular number
(one field at a time). My pronlem is that as well as each member having
a few main Tel numbers (Home, Work, Mobile and Fax) I have a seperate
table for each member which displays the Tel billing history for the
past month, and this table will have literally hundreds and hundreds of
numbers that each member has phoned. I need to search for the same
number in both tables across multiple fields...

Jan
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Darryl Kerkeslager]

"Jan Szymczuk" <sz******@btinternet.com> wrote

Thanks for your solution, but I already know about placing all the TelNo
fields in one table and running one query to find any particular number
(one field at a time). My pronlem is that as well as each member having
a few main Tel numbers (Home, Work, Mobile and Fax) I have a seperate
table for each member which displays the Tel billing history for the
past month, and this table will have literally hundreds and hundreds of
numbers that each member has phoned. I need to search for the same
number in both tables across multiple fields...

I don't see why you have so many tables and so many fields. Each table
should represent a distinct entity - to have one table of phone numbers
called for each member is not only making your task more difficult, it's not
normal(ized). A member is a distinct entity. A member's phone number is a
distinct entity. A phone number called is a distinct entity. Who called
that number is a property of the phone number called, as is the duration of
the call. Once you properly assign the entities to tables, and make the
attributes into fields, the query becomes simple.

Table: phone
phone_id
phone_number
phone_type_id
phone_member_id

Table: call
call_id
call_number
call_member_id
call_duration

I probably shouldn't do SQL off the cuff, but something like:

SELECT phone_member_id, call_member_id
FROM phone INNER JOIN call ON phone.phone_number = call.call_number;
Darryl Kerkeslager