Problems with a query

[kkleung89@gmail.com]

I have a particular query I need to write, but I'm not quite sure how
to do it. Basically, there's a table of appointments, which has a date
and a PetID, which links it to a pet table, which has a pet name. The
pet table has a CustomerID, which links it to a customer table, which
contains a phone number. How do I make a query that can get me the
date, the pet name, and the phone number simulataneously ? I figured I
could probably write several queries dependent upon each other to
compile the information from each level, but I'd imagine there's a
better way. Thanks.

[Fred Zuckerman]

Re: Problems with a query

<kkleung89@gmai l.comwrote in message
news:1153247667 .459687.155440@ b28g2000cwb.goo glegroups.com.. .

I have a particular query I need to write, but I'm not quite sure how
to do it. Basically, there's a table of appointments, which has a date
and a PetID, which links it to a pet table, which has a pet name. The
pet table has a CustomerID, which links it to a customer table, which
contains a phone number. How do I make a query that can get me the
date, the pet name, and the phone number simulataneously ? I figured I
could probably write several queries dependent upon each other to
compile the information from each level, but I'd imagine there's a
better way. Thanks.

(Making some assumptions on field names)

SELECT tblAppts.ApptDa te, tblPets.PetName , tblCusts.Phone
FROM (tblAppts LEFT JOIN tblPets ON tblAppts.PetID = tblPets.PetID) LEFT
JOIN tblCusts ON tblPets.CustID = tblCusts.CustID ;

Fred Zuckerman

[Larry Linson]

Re: Problems with a query


<kkleung89@gmai l.comwrote in message
news:1153247667 .459687.155440@ b28g2000cwb.goo glegroups.com.. .

>I have a particular query I need to write, but I'm not quite sure how
to do it. Basically, there's a table of appointments, which has a date
and a PetID, which links it to a pet table, which has a pet name. The
pet table has a CustomerID, which links it to a customer table, which
contains a phone number. How do I make a query that can get me the
date, the pet name, and the phone number simulataneously ? I figured I
could probably write several queries dependent upon each other to
compile the information from each level, but I'd imagine there's a
better way. Thanks.

In the Query Builder, add all three tables: Appointments, Pets, and
Customer. Click and drag from PetID in Appointments to PedID in the Pet
Table. Then click and drag from Customer ID in the Pet table to CustomerID
in the Customer Table. Drag down the needed information from the three
tables... date from Appointments, Pet Name from Pets, and Customer Name and
Phone from Customers.

Larry Linson
Microsoft Access MVP

[Rich P]

Re: Problems with a query

In the Query Designer select design view. Then you can show the 3
tables you mentioned. Select your ApptTbl and click on the Add button,
the select PetTbl - click Add, select CustTbl - click Add. THen Click
on the PetID field in the ApptTbl and DRAG it to the PetID field in the
PetTbl. If you can't see the PetID field in the list then scroll the
list until you can see the PetID field in each table in the Query
Designer. Then Click and drag the CustomerID field from the PetTbl to
the CustTbl.

Now you have joined the 3 tables. In the Field Row in the Query
designer you can now select the table and field you want to display.
Note: when you run the query, it will display all the records in your
system.

If you want to filter the resultset to only show records for one
customer you can enter the customer number under the customerID field
(if you added that field to the Field list to display) in the criteria
row. Now you will only display records for that customerID. Or you
could do the same if you only want to see records for a given Pet. Just
enter the PetID number under the criteria for the PetID field (assuming
you have selected PetID in the Fields to display row).

Rich

*** Sent via Developersdex http://www.developersdex.com ***