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

  • Thread starter Thread starter Jan Szymczuk
  • Start date Start date
J

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
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

One of the main reasons you are running into difficulties is that you
have failed to properly normalize your TablePeople table. You should
have factored-out the phone numbers into another table. Therefore, your
tables should look like this (:

CREATE TABLE People (
PeopleID COUNTER UNIQUE, -- AutoNumber column
Name varchar(50) not null,
Addr varchar(50)
... other columns ? ...
)
You'll have to decide which column to use as your PK. I set the
PeopleID as a unique index so the table Phones can use it as an FK.

The table People should be the "dominate" table & Members should be the
dependent table, which means there can be People who are not Members:

CREATE TABLE Members (
MembershipNo integer not null unique,
-- MembershipNo could be a varchar if it contains ltrs
PeopleID integer not null references People,
DateCreated date not null,
OfficeID integer references Offices ,
-- If a person can be a "assigned" to more than one
-- office OfficeID and PeopleID shud be in a separate table.
-- Offices is a table that holds info about offices,
-- including location.
CONSTRAINT PK_Members PRIMARY KEY (PeopleID(
)

CREATE TABLE PhoneTypes (
PhoneTypeID COUNTER NOT NULL UNIQUE,
PhoneTypeName VARCHAR(50) NOT NULL PRIMARY KEY
)

I set PhoneTypeID to Unique so Phones can use it as an FK.

CREATE TABLE Phones (
PhoneID COUNTER NOT NULL UNIQUE,
PeopleID integer not null references People,
PhoneTypeID integer not null references PhoneTypes ,
PhoneNumber varchar(10) not null, - like "9995551212"
CONSTRAINT PK_Phones PRIMARY KEY (PeopleID, PhoneType)
)

I set PhoneID as a Unique index so table Bills can use it as an FK. I
set the PK to PeopleID & PhoneType, which means only one PhoneType per
person can be allowed and allows the same phone number to be each of
those types: IOW, the Work & Home numbers can be the same.

CREATE TABLE Bills (
PeopleID integer not null references People,
PhoneID integer not null references Phones,
DateCalled date not null, -- includes date & time
Duration integer not null , -- time in seconds
CONSTRAINT PK_Bills Primary Key (PeopleID, PhoneID, DateCalled)
)

By having the phone number in only one table you only have one table to
search for a phone number. Since you want to know who called whom the
query will be complex. You need to JOIN from People down to Bills to
get the caller and then back up from Phones to People to get the
receiver of the call.

This query is untested, but a start. Perhaps someone else can get it to
work, if it doesn't work as is.

SELECT C.[Name] As Caller, R.[Name] As Receiver

FROM People As C INNER JOIN
(Bills As B INNER JOIN
(Phones As P INNER JOIN People AS R ON P.PeopleID = R.PeopleID)
ON B.PhoneID = P.PhoneID)
ON C.PeopleID = B.PeopleID,

WHERE P.PhoneNumber Like "*" & [Phone Number?] & "*"

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQfAqrIechKqOuFEgEQJA1gCfX74mzN+HstaHyt9dAcj5ULbpNfIAn2it
ikI3oim7UxsYX/xKQrWrKHsB
=x46U
-----END PGP SIGNATURE-----


Jan said:
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!
Click to expand...
 
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!
Click to expand...

Put your criterion under each of the telephone number fields, on
*separate lines* of the Query grid so that Access will use "OR" logic
to find the entered number in any one of the fields.

You might want to consider restructuring your tables: if you have a
one-to-many relationship between people and phone numbers (as you do),
a more normalized design would be to have a one-to-many relationship
from the Person table to a Phones table with fields for PersonID,
PhoneNumber, and PhoneType. If someone has five numbers, there'd be
five *records* in this table; but searching the single PhoneNumber
field would find any of them.

John W. Vinson[MVP]
 
Back
Top