Tough SQL objective - pulling data from 4 different tables

[MLH]

I have tblVehicles, tblOwners, tblProxies, tblLienholders.
In the vehicles table are [OwnerID] and [ProxyID] identifying
owner and proxy (generally an attorney). Vehicles table is
child table to tblOwners and tblProxies. tblLienholders has
[VehicleJobID] and is a child table to vehicles table.

As you can imagine, owners, proxies and lienholders houses
names & addresses of persons/companies. At some point, I need
to extract a dynaset of vehicles and these other entities -
each in a record by itself. For instance, for a given vehicle -
say their were 2 lienholders, an owner and an attorney proxy.
I would like to produce a 4-record dynaset as follows:

'79 VW John Q Owner 109 Lazy Lane Raleigh NC 27615
'79 VW Alfred Attorney 22 Main St Cary NC 27511
'79 VW ABC Loans 19 Castle Ave New York NY 00211
'79 VW Car Quest 5491 Minnesota Anchorage AK 99518

Is that even remotely possible? Here are table structures...

tblVehicles tblOwners tblProxies tblLienholders
[VehicleJobID] [OwnerID] [ProxyID] [LienHolderID]
[OwnerID] [OwnAddr] [ProxAddr] [VehicleJobID]
[ProxyID] blah... blah... [LHaddr]
[VIN] blah... blah... blah...
[MakeModel] blah... blah... blah...
blah...
blah...
blah...

[deko]

>I have tblVehicles, tblOwners, tblProxies, tblLienholders.

In the vehicles table are [OwnerID] and [ProxyID] identifying
owner and proxy (generally an attorney). Vehicles table is
child table to tblOwners and tblProxies. tblLienholders has
[VehicleJobID] and is a child table to vehicles table.

As you can imagine, owners, proxies and lienholders houses
names & addresses of persons/companies. At some point, I need
to extract a dynaset of vehicles and these other entities -
each in a record by itself. For instance, for a given vehicle -
say their were 2 lienholders, an owner and an attorney proxy.
I would like to produce a 4-record dynaset as follows:

'79 VW John Q Owner 109 Lazy Lane Raleigh NC 27615
'79 VW Alfred Attorney 22 Main St Cary NC 27511
'79 VW ABC Loans 19 Castle Ave New York NY 00211
'79 VW Car Quest 5491 Minnesota Anchorage AK 99518

Is that even remotely possible? Here are table structures...

tblVehicles tblOwners tblProxies tblLienholders
[VehicleJobID] [OwnerID] [ProxyID] [LienHolderID]
[OwnerID] [OwnAddr] [ProxAddr] [VehicleJobID]
[ProxyID] blah... blah... [LHaddr]
[VIN] blah... blah... blah...
[MakeModel] blah... blah... blah...
blah...
blah...
blah...

What I would do is start with 2 or 3 queries and select the results you need
from those queries. Consolidate as you can. Use the query designer for
help with the joins if you need to.

[MLH]

If it cannot be done with one query, I'll have to do it using DAO -
walking the records manually. Won't be as elegant. Would much
rather do it from SQL.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

What I would do is start with 2 or 3 queries and select the results you need
from those queries. Consolidate as you can. Use the query designer for
help with the joins if you need to.

[Tom van Stiphout]

On Mon, 26 Sep 2005 22:49:56 -0400, MLH <CR**@NorthState.net> wrote:

Sounds possible, most likely using a 4-part union query. Each part
goes after one type of entity. I would recommend adding a column just
to identify the entitytype.

-Tom.

I have tblVehicles, tblOwners, tblProxies, tblLienholders.
In the vehicles table are [OwnerID] and [ProxyID] identifying
owner and proxy (generally an attorney). Vehicles table is
child table to tblOwners and tblProxies. tblLienholders has
[VehicleJobID] and is a child table to vehicles table.

As you can imagine, owners, proxies and lienholders houses
names & addresses of persons/companies. At some point, I need
to extract a dynaset of vehicles and these other entities -
each in a record by itself. For instance, for a given vehicle -
say their were 2 lienholders, an owner and an attorney proxy.
I would like to produce a 4-record dynaset as follows:

'79 VW John Q Owner 109 Lazy Lane Raleigh NC 27615
'79 VW Alfred Attorney 22 Main St Cary NC 27511
'79 VW ABC Loans 19 Castle Ave New York NY 00211
'79 VW Car Quest 5491 Minnesota Anchorage AK 99518

Is that even remotely possible? Here are table structures...

tblVehicles tblOwners tblProxies tblLienholders
[VehicleJobID] [OwnerID] [ProxyID] [LienHolderID]
[OwnerID] [OwnAddr] [ProxAddr] [VehicleJobID]
[ProxyID] blah... blah... [LHaddr]
[VIN] blah... blah... blah...
[MakeModel] blah... blah... blah...
blah...
blah...
blah...

[Douglas J. Steele]

There's nothing wrong with having multiple queries.

For instance, you can join two of the tables the way you need to and save
that as, say, Query1. You then join Query1 to another of the tables and save
that as Query2. Finally, you join Query2 to the 4th table and save that as
Query3. You then use Query3 everywhere you need the data.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"MLH" <CR**@NorthState.net> wrote in message
news:a4********************************@4ax.com...

If it cannot be done with one query, I'll have to do it using DAO -
walking the records manually. Won't be as elegant. Would much
rather do it from SQL.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

What I would do is start with 2 or 3 queries and select the results you
need
from those queries. Consolidate as you can. Use the query designer for
help with the joins if you need to.

[MLH]

Sounds like a plan. I'll have to read up a bin on union queries
a bit before starting. I'm not familiar with them.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Sounds possible, most likely using a 4-part union query. Each part
goes after one type of entity. I would recommend adding a column just
to identify the entitytype.

-Tom.

I have tblVehicles, tblOwners, tblProxies, tblLienholders.
In the vehicles table are [OwnerID] and [ProxyID] identifying
owner and proxy (generally an attorney). Vehicles table is
child table to tblOwners and tblProxies. tblLienholders has
[VehicleJobID] and is a child table to vehicles table.

As you can imagine, owners, proxies and lienholders houses
names & addresses of persons/companies. At some point, I need
to extract a dynaset of vehicles and these other entities -
each in a record by itself. For instance, for a given vehicle -
say their were 2 lienholders, an owner and an attorney proxy.
I would like to produce a 4-record dynaset as follows:

'79 VW John Q Owner 109 Lazy Lane Raleigh NC 27615
'79 VW Alfred Attorney 22 Main St Cary NC 27511
'79 VW ABC Loans 19 Castle Ave New York NY 00211
'79 VW Car Quest 5491 Minnesota Anchorage AK 99518

Is that even remotely possible? Here are table structures...

tblVehicles tblOwners tblProxies tblLienholders
[VehicleJobID] [OwnerID] [ProxyID] [LienHolderID]
[OwnerID] [OwnAddr] [ProxAddr] [VehicleJobID]
[ProxyID] blah... blah... [LHaddr]
[VIN] blah... blah... blah...
[MakeModel] blah... blah... blah...
blah...
blah...
blah...

[Salad]

MLH wrote:

Sounds like a plan. I'll have to read up a bin on union queries
a bit before starting. I'm not familiar with them.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
And don't discount what was said about multiple queries mentioned by
others. Create a base query (Q1) and then use Q1 in Q2, then Q3 in Q3.
It's fast, quick, works great.

Sounds possible, most likely using a 4-part union query. Each part
goes after one type of entity. I would recommend adding a column just
to identify the entitytype.

-Tom.

I have tblVehicles, tblOwners, tblProxies, tblLienholders.
In the vehicles table are [OwnerID] and [ProxyID] identifying
owner and proxy (generally an attorney). Vehicles table is
child table to tblOwners and tblProxies. tblLienholders has
[VehicleJobID] and is a child table to vehicles table.

As you can imagine, owners, proxies and lienholders houses
names & addresses of persons/companies. At some point, I need
to extract a dynaset of vehicles and these other entities -
each in a record by itself. For instance, for a given vehicle -
say their were 2 lienholders, an owner and an attorney proxy.
I would like to produce a 4-record dynaset as follows:

'79 VW John Q Owner 109 Lazy Lane Raleigh NC 27615
'79 VW Alfred Attorney 22 Main St Cary NC 27511
'79 VW ABC Loans 19 Castle Ave New York NY 00211
'79 VW Car Quest 5491 Minnesota Anchorage AK 99518

Is that even remotely possible? Here are table structures...

tblVehicles tblOwners tblProxies tblLienholders
[VehicleJobID] [OwnerID] [ProxyID] [LienHolderID]
[OwnerID] [OwnAddr] [ProxAddr] [VehicleJobID]
[ProxyID] blah... blah... [LHaddr]
[VIN] blah... blah... blah...
[MakeModel] blah... blah... blah...
blah...
blah...
blah...

[MLH]

Many thx to all of you. UNION SELECT works GREAT.

[David W. Fenton]

MLH <CR**@NorthState.net> wrote in
news:2k********************************@4ax.com:

I have tblVehicles, tblOwners, tblProxies, tblLienholders.
In the vehicles table are [OwnerID] and [ProxyID] identifying
owner and proxy (generally an attorney). Vehicles table is
child table to tblOwners and tblProxies. tblLienholders has
[VehicleJobID] and is a child table to vehicles table.

Seems to me like you have an erroneous schema.

You have vehicles and people. Put all the people in one table and
use an N:N join (with an intermediate table) to define what the
person's relationship to the car is. You'd then have:

tblVehicle : PK: VehicleID
tblPerson : PK: PersonID
tlbVehiclePerson : PK: VehicleID/PersonID (compound)

In tblVehiclePerson you'd have an additional field to define whether
that person is the owner, attorney or lien holder.

As lien holders are likely institutions, your person table may be
the wrong apporoach, and you'd need some generalized kind of
person/organization table. Of course, I often store organizations in
my people table, simply using the last name field as the entity
name.

But you really have relationships between two different distinct
entities, with one of those entities have 3 subtypes (owner,
attorney, lien holder). If your schema represents that, it's *much*
easier (and much more efficient) to get data about a vehicle's
related entities.

Another advantage is that a single person can then be related to
multiple vehicles. You could have the attorney's car, with an Owner
link, as well as the cars for which the attorney is the legal
represenative. And so forth.

The fact that this works shows that it's probably a much better
schema, whereas your schema requires putting the data about a single
person in more than one table. That's a clue right there that
there's something off with your underlying schema.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

MLH <CR**@NorthState.net> wrote in
news:qq********************************@4ax.com:

Many thx to all of you. UNION SELECT works GREAT.

Keep in mind that you can use UNION ALL to speed things up, as it
will not filter out duplicates (which you shouldn't have).

But I think your schema is wrong in the first place. You wouldn't
need a UNION if you used the right schema (I explained it in another
post).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[MLH]

You are probably on-the-money with those suggestions.
I was ill informed at the beginning - much needed design
level information didn't come to me until near the end of
the project. For instance, I had no idea I would need
proxies until a couple of days ago. And yes, I made
some decisions w/o thinking much about them. I do
like the vehicles 'n people clear distinction. I'll likely
clean this up in a future rev.

[David W. Fenton]

MLH <CR**@NorthState.net> wrote in
news:p0********************************@4ax.com:

You are probably on-the-money with those suggestions.
I was ill informed at the beginning - much needed design
level information didn't come to me until near the end of
the project. For instance, I had no idea I would need
proxies until a couple of days ago. And yes, I made
some decisions w/o thinking much about them. I do
like the vehicles 'n people clear distinction. I'll likely
clean this up in a future rev.

The great thing about that structure is that it easily supports the
addition of different subtypes in data without needing to add or
restructure tables.

Almost all of my apps are structured with some variation of the
people vs. all other entities. It just seems to me to be one of the
most common basic data schemas that it's just what I've settled on.

The only question in that schema is how you store addresses, and
then things can get really hairy.

But that's a separate issue entirely.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Tim Marshall]

MLH wrote:

I do
like the vehicles 'n people clear distinction. I'll likely
clean this up in a future rev.

Far, far better to do it now. People will get used to the way your app
works and worse, you'll get used to it. You'll never get around to
revising it. If you do, it will generate a tremendous amount of
headaches, because you'll have lots and lots data that may not fit as
easily into the properly designed schema David's suggested.

And an optimized design will allow you to add features much more easily
in the future.

Besides, nothing makes one's legacy stink as much people coming in
behind you to clean up messes. 8)

Don't settle on the design you have, you will ultimately regret it.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tom van Stiphout]

On Wed, 28 Sep 2005 17:30:20 -0230, Tim Marshall
<TI****@PurplePandaChasers.Moertherium> wrote:

I second that.
-Tom.

MLH wrote:

I do
like the vehicles 'n people clear distinction. I'll likely
clean this up in a future rev.

Far, far better to do it now. People will get used to the way your app
works and worse, you'll get used to it. You'll never get around to
revising it. If you do, it will generate a tremendous amount of
headaches, because you'll have lots and lots data that may not fit as
easily into the properly designed schema David's suggested.

And an optimized design will allow you to add features much more easily
in the future.

Besides, nothing makes one's legacy stink as much people coming in
behind you to clean up messes. 8)

Don't settle on the design you have, you will ultimately regret it.