[AccessD] What type of query....

This query works (note: this is SQL 2005 syntax; to make it work in Access, lose the schema-part of the name, i.e. "ZIps.").

<code>

SELECT HomeType.TypeID, HomeType.TYPE, Zips.People.FName, Zips.People.LName, HomeZips.Zip AS Home, WorkZips.Zip AS [Work]
FROM Zips.People INNER JOIN
Zips.Zips AS HomeZips ON Zips.People.PID = HomeZips.PID INNER JOIN
Zips.Types AS HomeType ON HomeZips.TypeID = HomeType.TypeID INNER JOIN
Zips.Zips AS WorkZips ON Zips.People.PID = WorkZips.PID INNER JOIN
Zips.Types AS WorkType ON WorkZips.TypeID = WorkType.TypeID
WHERE (HomeType.TYPE = 'Home')
AND (WorkType.TYPE = 'Work')
</code>

The trick is to open the Zips table twice qualifying each instance with a criterion that points to 'Home' or 'Work'.

hth, 
Arthur Fuller
Technical Writer, Data Modeler, SQL Sensei
Artful Databases Organization
www.artfulsoftware.com




----- Original Message ----
From: Bobby Heid <bheid at sc.rr.com>
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Sent: Thursday, February 8, 2007 11:26:16 PM
Subject: [AccessD] What type of query....


Hey,

I have this scenario

Table 1:
PID
FName
Lname

Table2:
ZID
PID
TypeID
Zip

Table3:
TypeID
Type

Note that there are only 2 types, home and work.

What I want is to have:
Fname       Lname               HomeZip    WorkZip
Joe            Schmoe          29977        78665
Suzy         Chapstick           98847

To do this in one query, what kind of query would I use?  Basically, I have
a one-to-many relationship and I want to have both zips for each name
record.

Thanks,
Bobby


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com