[AccessD] What type of query....

Hi Arthur,

Thanks for that.  One other issue.  There may not be a given type of zip (or
I guess even no zip in the Zip (table 2) table.  This query returns those
records that have both.  Any ideas?

Thanks,
Bobby 
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of artful at rogers.com
Sent: Friday, February 09, 2007 8:08 AM
To: Access Developers discussion and problem solving
Subject: Re: [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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com