Query help

[John Smith]

I've been too far away from SQL for too long doing other-level things and my
ancient addled brain isn't what it once was.

Here's the deal:
My wife is doing a small app in Access. I know zippo about Access and any
peculiarities it may have - nor do I want to learn anything about this
particular piece of Microsft virusware. Needless to say, she's asking me
loads of questions.

She's trying to keep track of all addresses used by various people, and also
needs to ensure that each person has at least one address (there is a null
address available for those people who she doesn't have full details for at
the time of original data input).

That said, this isn't strictly a question about Access (97 in her case). I'm
just trying to see if I'm bumping into a limitation in the version of Access
regarding outer joins or whether my brain is simply on stun mode these days
vis. SQL-92.

The data model I'm working with looks like the following:

Person Address
| |
|-->PersonAddress<---|
^
|
AddressType

I'm using surrogate keys (autonumber) for all tables. Sample instance data
appears below (assume all attributes are nullable) along with the desired
query results:
Person Table
PersonPK fname lname
1 Tom Sawyer
2 Huck Finn
3 Eric Clapton
4 Louis Armstrong
5 George Bush
6 Kevin Bacon
7 Buffalo Bob
Address Table (all ficitious)
AddressPK street city state
1 2200 Your Street Mudhole MO
2 8 My Street Boston MA
3 461 Ocean Blvd Miami FLA
4 10 His Street New York NY
5 1600 Pennsylvania Washington D.C
6 R.R. 5 Dustbowl TX
7 Fort Leavenworth Leavenworth KS
8 100 Dow Way Midland MI
9 256 Chemical Way Ann Arbor MI
10 null null
null
AddressType Table
AddressTypePK desc
1 home
2 work
3 vacation
4 prison
5 other
PersonAddress Table
personPK addressPK addressTypePK
1 1 1
2 1 1
3 2 1
3 3 3
4 4 1
5 5 2
5 6 1
5 7 4
6 8 1
6 9 2
7 10 5
Desired results of the query
====================
fname lname street city
state desc
Tom Sawyer 2200 Your Street Mudhole MO home
Huck Finn 2200 Your Street Mudhole MO
home
Eric Clapton 8 My street Boston MA
home
Eric Clapton 461 Ocean Blvd Miami FLA
vacation
Louis Armstrong 10 His Street New York NY
home
George Bush 1600 Pennsylvania Washington D.C work
George Bush R.R.5 Dustbowl TX
home
George Bush Fort Leavenworth Leavenworth KS prison
Kevin Bacon 100 Dow Way Midland MI
work
Kevin Bacon 256 Chemical Way Ann Arbor MI home
Buffalo Bob null null
null other
(sorry about the wrapping)

Clearly there is a left outer join involved in order to return Buffalo Bob
with no known address.

Right now I'm stumped. I know that I'm capable of figuring this out in the
remaing time the universe has left to exists, but that would just make me
plain ordinary stupid. So I prefer to claim that I have Alzheimer's and thus
I'm not responsible for my stupidity. It may be a fine distinction but it's
one I have to invoke.

Anything you can do to help clear the cobwebs from my brain are appreciated.

[Phil Stanton]

Seems you are doing fine.
Have just spent 5 minutes writing a micky mose version. If you want I will
e-mail it to you

Phil

"John Smith" <[e-mail]> wrote in message
news:HP*******************@news04.bloor.is.net.cab le.rogers.com...

I've been too far away from SQL for too long doing other-level things and my ancient addled brain isn't what it once was.

Here's the deal:
My wife is doing a small app in Access. I know zippo about Access and any
peculiarities it may have - nor do I want to learn anything about this
particular piece of Microsft virusware. Needless to say, she's asking me
loads of questions.

She's trying to keep track of all addresses used by various people, and also needs to ensure that each person has at least one address (there is a null
address available for those people who she doesn't have full details for at the time of original data input).

That said, this isn't strictly a question about Access (97 in her case). I'm just trying to see if I'm bumping into a limitation in the version of Access regarding outer joins or whether my brain is simply on stun mode these days vis. SQL-92.

The data model I'm working with looks like the following:

Person Address
| |
|-->PersonAddress<---|
^
|
AddressType

I'm using surrogate keys (autonumber) for all tables. Sample instance data
appears below (assume all attributes are nullable) along with the desired
query results:
Person Table
PersonPK fname lname
1 Tom Sawyer
2 Huck Finn
3 Eric Clapton
4 Louis Armstrong
5 George Bush
6 Kevin Bacon
7 Buffalo Bob
Address Table (all ficitious)
AddressPK street city state
1 2200 Your Street Mudhole MO
2 8 My Street Boston MA
3 461 Ocean Blvd Miami FLA
4 10 His Street New York NY
5 1600 Pennsylvania Washington D.C
6 R.R. 5 Dustbowl TX 7 Fort Leavenworth Leavenworth KS
8 100 Dow Way Midland MI
9 256 Chemical Way Ann Arbor MI
10 null null
null
AddressType Table
AddressTypePK desc
1 home
2 work
3 vacation
4 prison
5 other
PersonAddress Table
personPK addressPK addressTypePK
1 1 1
2 1 1
3 2 1
3 3 3
4 4 1
5 5 2
5 6 1
5 7 4
6 8 1
6 9 2
7 10 5
Desired results of the query
====================
fname lname street city
state desc
Tom Sawyer 2200 Your Street Mudhole MO home
Huck Finn 2200 Your Street Mudhole MO
home
Eric Clapton 8 My street Boston MA
home
Eric Clapton 461 Ocean Blvd Miami FLA
vacation
Louis Armstrong 10 His Street New York NY
home
George Bush 1600 Pennsylvania Washington D.C work
George Bush R.R.5 Dustbowl TX
home
George Bush Fort Leavenworth Leavenworth KS prison
Kevin Bacon 100 Dow Way Midland MI
work
Kevin Bacon 256 Chemical Way Ann Arbor MI home
Buffalo Bob null null
null other
(sorry about the wrapping)

Clearly there is a left outer join involved in order to return Buffalo Bob
with no known address.

Right now I'm stumped. I know that I'm capable of figuring this out in the remaing time the universe has left to exists, but that would just make me
plain ordinary stupid. So I prefer to claim that I have Alzheimer's and thus I'm not responsible for my stupidity. It may be a fine distinction but it's one I have to invoke.

Anything you can do to help clear the cobwebs from my brain are appreciated.