Okay so i have a situation where I'm putting together an SQL
statement.  The database contains life insurance policy information.

The way the tables are structured:

Policy Table-all Policy Information
Person Table-all People in our Database, regardless of role (Policy
Owner, Advisor, Trustee, etc.)
Trust Table-all Insurance Trust Information
Address Table-all address info, with a flag 'Address Type' field
indicator showing an individual's primary, seasonal, and work
addresses.

What i'm trying to do is get the query to pull each policy, and for
each policy, give EITHER the policy owner's address info (primary,
seasonal, and work, if information on all those addresses is contained
in the database), OR, if a trust owns the policy, then to find the
Trustee (a field contained within the 'Trusts' table and linked by a
'Person ID' which is associated with the 'Person' table, and for that
Trustee list their home, work, and/or seasonal address.

The problem that I run into is when I set up the relationships to link
the 'Policy Owner' field to the 'Person Table' via their 'Person ID',
I can't seem to get the same result for a Trustee in the event that
the policy is trust-owned. I set up this subquery basically, that in
plain English is saying 'If the policy is trust-owned create a link
between the Trust table and the policy table, and another link between
the Trust table and the Person table, and create another link between
the Person table and the Address table, and pull all that person's
address info.

I think the reason for this is that the Policy-Person-Address link for
person-owned policies is created in the query design view, and as a
result, if say that person has 3 separate addresses, for one policy
you'll have 3 records, one listing 'primary, one listing 'seasonal,'
and one listing 'work.'

BUT the links between the Policy-Trust-Person-Address aren't created
in the query design view, they are actually included in a
sub-query...so it looks like what is happening is that in the event
that a person (Trustee) has 3 addresses, it's trying to list all three
in a single record (created by the main query)

So i get the 'At most one record can be returned from this subquery'

Any thoughts or ideas on where i'm going wrong? Sorry if i left
anything major but I did my best to explain this as concisely as
possible for a query that seems pretty complex (10,589 characters)

Thanks in advance,

Jeremy