[AccessD] What type of query....

A simple way to do it is to break the query into 3 pieces. The table People, plus two views, one for HomeZips and the other for WorkZips:

<code>
CREATE VIEW [dbo].[Zips_HomeZips_vue]
AS
SELECT Zips.People.PID, Zips.People.FName, Zips.People.LName, Zips.Zips.Zip AS Home, Zips.Types.TYPE
FROM Zips.People INNER JOIN
Zips.Zips ON Zips.People.PID = Zips.Zips.PID INNER JOIN
Zips.Types ON Zips.Zips.TypeID = Zips.Types.TypeID
WHERE (Zips.Types.TYPE = 'Home')
GO

CREATE VIEW [dbo].[Zips_WorkZips_vue]
AS
SELECT Zips.People.PID, Zips.People.FName, Zips.People.LName, Zips.Zips.Zip AS [Work], Zips.Types.TYPE
FROM Zips.People INNER JOIN
Zips.Zips ON Zips.People.PID = Zips.Zips.PID INNER JOIN
Zips.Types ON Zips.Zips.TypeID = Zips.Types.TypeID
WHERE (Zips.Types.TYPE = 'Work')
GO
</code>

Now just join them all up and run the query:

<code>
SELECT Zips.People.FName, Zips.People.LName, Zips_HomeZips_vue.Home, Zips_WorkZips_vue.[Work]
FROM Zips.People 
LEFT OUTER JOIN
Zips_HomeZips_vue ON Zips.People.PID = Zips_HomeZips_vue.PID 
LEFT OUTER JOIN
Zips_WorkZips_vue ON Zips.People.PID = Zips_WorkZips_vue.PID
</code>
 
Note: I like to do things this way because the components are reusable in other contexts, whereas doing the whole thing in a single query solves only the specific problem. I have written about this approach in various places. I call it "atomic and molecular queries". In this case the views are the atoms and the query is the molecule comprised of said atoms.

hth,

Arthur 


----- Original Message ----
From: Bobby Heid <bheid at sc.rr.com>
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Sent: Friday, February 9, 2007 10:03:31 AM
Subject: Re: [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