confusing relationships

Hi Tom,

Thanks for the reply...It has got me thinking as to what needs to be done.
Now, I'll let you know the situation in detail...

The contact is the person who has signed to have his insurance taken care of
by the company that needs this database.

This person(primary contact) can have one (lets assume one for simplicity)
wife/husband(spouse) whose insurance policy is also handled by the company.
Simillarly he can have multiple children each with insurances handled by the
company.

I have a contact table, which has all the details like, name , dob, address,
etc. The primary key is an autonumber Contact_ID

The insurance is a combination of different things that make up a policy,
like insurance number date purchased, date of renewal, premium, insurance
company , comission that this company gets to manage the insurance etc.

I understand that the insurance can be many types like life insurance,
income protection etc.To achieve this, I have another table called [Policy],
which has all the different types of policies.

There is another table called [Insurance Policy]
{id}
{Insurance ID}
{Policy Type} -- Combo
box with row source query =Select Policy Type from [Policy]
This table is linked to the Insurance Table insurance table with many - 1
(respectively) == For every insurance record, the [Insurance Policy] table
will have multiple entries.

Now all i need to do is to be able to achieve this with all details of
contacts, his/her spouse and their insurance details ...

Hope that gives you a better idea..

Thanks

kingnothing


Tom Ellison wrote:
Dear Kingnothing:

I've been reading what you and other have posted. What I'm concerned about
is knowing what the entities are referenced by the table you propose to
have.

Let's concentrate for a moment on the Insurance table. Is this a table
where each row is one policy? Or is each row an insurance plan, to which
many insured persons may be associated? For some reason I am suspecting the
latter. In this case, the relationship between "insured persons" (contact
table?) and Insurance has to be many-to-many. Many persons may purchase any
given plan, and any person may purchase more than just one plan. Is that
the case?

"contact can have multiple insurances, children and spouses"

I don't know if I like the many-to-many relationship between contact and
spouse. I'm not sure whether you're modeling polygamy here, or divorces, or
widowed persons who have remaried. Is that your intent?

Before modeling the contact/spouse/children aspect, I suggest you get very
familiar with just what functions family relationships need to have in the
finished product. A person could be a spouse and a child (of his or her
parents). Just how far does this need to go?

Consider that a husband and wife are both spouses. They may both be
insured. In this sense, they are equivalent. Now if John and Mary are wed,
and each has a policy, then do you really want both to be contacts as well
as both being spouses. Think about this. If they move, you'd have 4 places
where you update their address and phone. That's not so functional!

This thing does not appear simple to me. I have handled such requirements,
but it is significantly complex. It's going to be fairly difficult to hash
through all of it in a newsgroup.

Hope this gives some insight.

Tom Ellison

Hi All,

[quoted text clipped - 42 lines]

kingnothing

--
Message posted via http://www.accessmonster.com

Just out of curiousity, why does anyone care if related people all have
insurance? Shouldn't a policy just be concerned with the person the policy
is taken out on? Why are you trying to track all of these combinations of
people?

"kingnothing via AccessMonster.com" wrote:

Hi Tom,

Thanks for the reply...It has got me thinking as to what needs to be done.
Now, I'll let you know the situation in detail...

The contact is the person who has signed to have his insurance taken care of
by the company that needs this database.

This person(primary contact) can have one (lets assume one for simplicity)
wife/husband(spouse) whose insurance policy is also handled by the company.
Simillarly he can have multiple children each with insurances handled by the
company.

I have a contact table, which has all the details like, name , dob, address,
etc. The primary key is an autonumber Contact_ID

The insurance is a combination of different things that make up a policy,
like insurance number date purchased, date of renewal, premium, insurance
company , comission that this company gets to manage the insurance etc.

I understand that the insurance can be many types like life insurance,
income protection etc.To achieve this, I have another table called [Policy],
which has all the different types of policies.

There is another table called [Insurance Policy]
{id}
{Insurance ID}
{Policy Type} -- Combo
box with row source query =Select Policy Type from [Policy]
This table is linked to the Insurance Table insurance table with many - 1
(respectively) == For every insurance record, the [Insurance Policy] table
will have multiple entries.

Now all i need to do is to be able to achieve this with all details of
contacts, his/her spouse and their insurance details ...

Hope that gives you a better idea..

Thanks

kingnothing


Tom Ellison wrote:
Dear Kingnothing:

I've been reading what you and other have posted. What I'm concerned about
is knowing what the entities are referenced by the table you propose to
have.

Let's concentrate for a moment on the Insurance table. Is this a table
where each row is one policy? Or is each row an insurance plan, to which
many insured persons may be associated? For some reason I am suspecting the
latter. In this case, the relationship between "insured persons" (contact
table?) and Insurance has to be many-to-many. Many persons may purchase any
given plan, and any person may purchase more than just one plan. Is that
the case?

"contact can have multiple insurances, children and spouses"

I don't know if I like the many-to-many relationship between contact and
spouse. I'm not sure whether you're modeling polygamy here, or divorces, or
widowed persons who have remaried. Is that your intent?

Before modeling the contact/spouse/children aspect, I suggest you get very
familiar with just what functions family relationships need to have in the
finished product. A person could be a spouse and a child (of his or her
parents). Just how far does this need to go?

Consider that a husband and wife are both spouses. They may both be
insured. In this sense, they are equivalent. Now if John and Mary are wed,
and each has a policy, then do you really want both to be contacts as well
as both being spouses. Think about this. If they move, you'd have 4 places
where you update their address and phone. That's not so functional!

This thing does not appear simple to me. I have handled such requirements,
but it is significantly complex. It's going to be fairly difficult to hash
through all of it in a newsgroup.

Hope this gives some insight.

Tom Ellison

Hi All,

[quoted text clipped - 42 lines]

kingnothing

--
Message posted via http://www.accessmonster.com

Dear Kingnothing:

I was toying with the idea of having just a single table of persons.
Self-referencing links could determine relationships (spouse, father,
mother). An attribute of this table could be Contact, a yes/no. Filtering
on this would give you only those currently in your contact table.

I would keep a separate table of addresses. Husband, wife, son, and
daughter could link to the same address record (including phone and other
details if desired) so that, when someone moves, everyone in a family group
would "move" together with only a single change.

This is a bare outline of just a portion of the system.

A complete job would be very considerable. But proper table designs are the
foundation of all the work that follows. If you get a bad table design, the
work you build on top of that can be a huge waste.

Normalization of tables isn't just some dry set of rules, not to me. It is
fundamental to the proper way of thinking about a database. The rules are
there to guide you to being able to properly analyze, categorize, and
synthesize (and any other "ize") the solution. The rules are natural and
essential ways of thinking.

Tom Ellison


"kingnothing via AccessMonster.com" u18754@uwe wrote in message
news:5dabfcf593273@uwe...
Hi Tom,

Thanks for the reply...It has got me thinking as to what needs to be done.
Now, I'll let you know the situation in detail...

The contact is the person who has signed to have his insurance taken care
of
by the company that needs this database.

This person(primary contact) can have one (lets assume one for simplicity)
wife/husband(spouse) whose insurance policy is also handled by the
company.
Simillarly he can have multiple children each with insurances handled by
the
company.

I have a contact table, which has all the details like, name , dob,
address,
etc. The primary key is an autonumber Contact_ID

The insurance is a combination of different things that make up a policy,
like insurance number date purchased, date of renewal, premium, insurance
company , comission that this company gets to manage the insurance etc.

I understand that the insurance can be many types like life insurance,
income protection etc.To achieve this, I have another table called
[Policy],
which has all the different types of policies.

There is another table called [Insurance Policy]
{id}
{Insurance ID}
{Policy Type} --
Combo
box with row source query =Select Policy Type from [Policy]
This table is linked to the Insurance Table insurance table with many - 1
(respectively) == For every insurance record, the [Insurance Policy]
table
will have multiple entries.

Now all i need to do is to be able to achieve this with all details of
contacts, his/her spouse and their insurance details ...

Hope that gives you a better idea..

Thanks

kingnothing


Tom Ellison wrote:
Dear Kingnothing:

I've been reading what you and other have posted. What I'm concerned
about
is knowing what the entities are referenced by the table you propose to
have.

Let's concentrate for a moment on the Insurance table. Is this a table
where each row is one policy? Or is each row an insurance plan, to which
many insured persons may be associated? For some reason I am suspecting
the
latter. In this case, the relationship between "insured persons" (contact
table?) and Insurance has to be many-to-many. Many persons may purchase
any
given plan, and any person may purchase more than just one plan. Is that
the case?

"contact can have multiple insurances, children and spouses"

I don't know if I like the many-to-many relationship between contact and
spouse. I'm not sure whether you're modeling polygamy here, or divorces,
or
widowed persons who have remaried. Is that your intent?

Before modeling the contact/spouse/children aspect, I suggest you get very
familiar with just what functions family relationships need to have in the
finished product. A person could be a spouse and a child (of his or her
parents). Just how far does this need to go?

Consider that a husband and wife are both spouses. They may both be
insured. In this sense, they are equivalent. Now if John and Mary are
wed,
and each has a policy, then do you really want both to be contacts as well
as both being spouses. Think about this. If they move, you'd have 4
places
where you update their address and phone. That's not so functional!

This thing does not appear simple to me. I have handled such
requirements,
but it is significantly complex. It's going to be fairly difficult to
hash
through all of it in a newsgroup.

Hope this gives some insight.

Tom Ellison

Hi All,

[quoted text clipped - 42 lines]

kingnothing

--
Message posted via http://www.accessmonster.com

Just out of curiousity, why does anyone care if related people all have
insurance? Shouldn't a policy just be concerned with the person the policy
is taken out on? Why are you trying to track all of these combinations of
people?

Thats one of the requirements that i have been given. Its because, if a
client (the main contat) calls this company , they should be able to retrive
all the information regarding his/her spouse and children.
Hi Tom,

[quoted text clipped - 82 lines]

kingnothing

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200603/1

Please see inline..

Tom Ellison wrote:
Dear Kingnothing:

I was toying with the idea of having just a single table of persons.
Self-referencing links could determine relationships (spouse, father,
mother). An attribute of this table could be Contact, a yes/no. Filtering
on this would give you only those currently in your contact table.


I would keep a separate table of addresses. Husband, wife, son, and
daughter could link to the same address record (including phone and other
details if desired) so that, when someone moves, everyone in a family group
would "move" together with only a single change.

This is what i have been told by all the kind souls that are trying to help
me. But what eevryone fails to understand is that i dont know how to do it.
If you can be kind enough and explain the same in a detailed fashin, it would
be very helpful. If you can, please represent it a little graphically for me
to understand.

I was also wondering if i should just leave the relationships out of the
table and just create joins in Queries and make those queries the source for
my forms. Is that how it works??

This is a bare outline of just a portion of the system.

A complete job would be very considerable. But proper table designs are the
foundation of all the work that follows. If you get a bad table design, the
work you build on top of that can be a huge waste.

I realise that and am striving to achieve that perfect database design .

Normalization of tables isn't just some dry set of rules, not to me. It is
fundamental to the proper way of thinking about a database. The rules are
there to guide you to being able to properly analyze, categorize, and
synthesize (and any other "ize") the solution. The rules are natural and
essential ways of thinking.

Tom Ellison

Hi Tom,

[quoted text clipped - 97 lines]

kingnothing

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200603/1

Dear Kingnothing:

Putting the relationships into the table design is an essential. There is
something called referential integrity.

If you have a one-to-many relationship you must not allow the rows on the
many side to be "orphaned". That means, you cannot delete a row in the
table on the "one" side when there are rows on the "many" side. You must
either delete them, too, or prohibit deleting the "one". That's the job of
referential integrity. Try it! You'll see what I mean.

So then, without enforcing referential integrity, there is little likelihood
the database will work well later when you write those queries.

Tom Ellison


"kingnothing via AccessMonster.com" u18754@uwe wrote in message
news:5db34c780bd21@uwe...
Please see inline..

Tom Ellison wrote:
Dear Kingnothing:

I was toying with the idea of having just a single table of persons.
Self-referencing links could determine relationships (spouse, father,
mother). An attribute of this table could be Contact, a yes/no.
Filtering
on this would give you only those currently in your contact table.


I would keep a separate table of addresses. Husband, wife, son, and
daughter could link to the same address record (including phone and other
details if desired) so that, when someone moves, everyone in a family
group
would "move" together with only a single change.

This is what i have been told by all the kind souls that are trying to
help
me. But what eevryone fails to understand is that i dont know how to do
it.
If you can be kind enough and explain the same in a detailed fashin, it
would
be very helpful. If you can, please represent it a little graphically for
me
to understand.

I was also wondering if i should just leave the relationships out of the
table and just create joins in Queries and make those queries the source
for
my forms. Is that how it works??

This is a bare outline of just a portion of the system.

A complete job would be very considerable. But proper table designs are
the
foundation of all the work that follows. If you get a bad table design,
the
work you build on top of that can be a huge waste.

I realise that and am striving to achieve that perfect database design .

Normalization of tables isn't just some dry set of rules, not to me. It
is
fundamental to the proper way of thinking about a database. The rules are
there to guide you to being able to properly analyze, categorize, and
synthesize (and any other "ize") the solution. The rules are natural and
essential ways of thinking.

Tom Ellison

Hi Tom,

[quoted text clipped - 97 lines]

kingnothing

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200603/1

Yeah i know about referential intergrity. so, you are saying that
relationships are absolutely imperative for the database design, right!!
Please look at http://members.westnet.com.au/mukund/rel.gif and tell if if
there is anything wrong with the way the database is designed (Apart form the
fact that i will be storing simillar records in different tables)

PS: if you want a clearer picture let me know, i can make another one

Regards,

kingnothing

Tom Ellison wrote:
Dear Kingnothing:

Putting the relationships into the table design is an essential. There is
something called referential integrity.

If you have a one-to-many relationship you must not allow the rows on the
many side to be "orphaned". That means, you cannot delete a row in the
table on the "one" side when there are rows on the "many" side. You must
either delete them, too, or prohibit deleting the "one". That's the job of
referential integrity. Try it! You'll see what I mean.

So then, without enforcing referential integrity, there is little likelihood
the database will work well later when you write those queries.

Tom Ellison

Please see inline..

[quoted text clipped - 51 lines]

kingnothing

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200603/1

Dear Kingnothing:

I have been looking at this site. Would you consider making it more useful?

Please make the width and height of the table rectangles large enough to be
able to see the width and height for all the columns and their names.

What I said about combining contact, spouse, and child into a single table
still stands, tentatively, as I have not really seen your design yet.
Please reveal it as I requested and post back here if you're interested.

Tom Ellison


"kingnothing via AccessMonster.com" u18754@uwe wrote in message
news:5db67b0fae5df@uwe...
Yeah i know about referential intergrity. so, you are saying that
relationships are absolutely imperative for the database design, right!!
Please look at http://members.westnet.com.au/mukund/rel.gif and tell if if
there is anything wrong with the way the database is designed (Apart form
the
fact that i will be storing simillar records in different tables)

PS: if you want a clearer picture let me know, i can make another one

Regards,

kingnothing

Tom Ellison wrote:
Dear Kingnothing:

Putting the relationships into the table design is an essential. There is
something called referential integrity.

If you have a one-to-many relationship you must not allow the rows on the
many side to be "orphaned". That means, you cannot delete a row in the
table on the "one" side when there are rows on the "many" side. You must
either delete them, too, or prohibit deleting the "one". That's the job
of
referential integrity. Try it! You'll see what I mean.

So then, without enforcing referential integrity, there is little
likelihood
the database will work well later when you write those queries.

Tom Ellison

Please see inline..

[quoted text clipped - 51 lines]

kingnothing

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200603/1

Your contact table would have an AddressID field. You would then have another
table containing all the address information. This would also have an
AddressID field which will be the primary key. You then create a relationship
between the two tables (open the relationships window, add the two tables and
drag one AddressID field on to the other, click "enforce referential
integrity".

Each contact in the same household would have the same AddressID.

Dave

"kingnothing via AccessMonster.com" wrote:

Please see inline..

Tom Ellison wrote:
Dear Kingnothing:

I was toying with the idea of having just a single table of persons.
Self-referencing links could determine relationships (spouse, father,
mother). An attribute of this table could be Contact, a yes/no. Filtering
on this would give you only those currently in your contact table.


I would keep a separate table of addresses. Husband, wife, son, and
daughter could link to the same address record (including phone and other
details if desired) so that, when someone moves, everyone in a family group
would "move" together with only a single change.

This is what i have been told by all the kind souls that are trying to help
me. But what eevryone fails to understand is that i dont know how to do it.
If you can be kind enough and explain the same in a detailed fashin, it would
be very helpful. If you can, please represent it a little graphically for me
to understand.

I was also wondering if i should just leave the relationships out of the
table and just create joins in Queries and make those queries the source for
my forms. Is that how it works??

This is a bare outline of just a portion of the system.

A complete job would be very considerable. But proper table designs are the
foundation of all the work that follows. If you get a bad table design, the
work you build on top of that can be a huge waste.

I realise that and am striving to achieve that perfect database design .

Normalization of tables isn't just some dry set of rules, not to me. It is
fundamental to the proper way of thinking about a database. The rules are
there to guide you to being able to properly analyze, categorize, and
synthesize (and any other "ize") the solution. The rules are natural and
essential ways of thinking.

Tom Ellison

Hi Tom,

[quoted text clipped - 97 lines]

kingnothing

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200603/1
.

Thanks a lot Vincent, I really appreciate all the work that has gone into
this reply. It is really helping me a lot.

During the course of the day, i will modify my message according to your
suggestion, and see how it goes.

Thanks

kingnothing

Vincent Johns wrote:
OK, here's a suggested (alternate) design for your Table structure.
Although I specified enforcing referential integrity for most links, I
did not do so with the [Person] Table, because it interfered with adding
new records.

All the primary keys (the ones identifying the record they're in) have
names consisting of the name of the Table, followed by "_ID". The
foreign keys (those used to refer to some other record) have similar
names, but possibly prefixed by a name suggesting what they contain,
such as [Parent_Person_ID] to refer to [Person_ID].

+----------+
|Person |
| _Client |
1|----------|
.--|Person_ID |
| +----------+
|
| +----------+ +------------+
| |Person | |Insurance |
| | _Child | | _Child | +---------+
| |----------|1 oo|------------| |Policy |
| oo|Person_ID |-----|Insured_ | | _Child |
+---|Parent_ | | Person_ID |1 oo|---------|
| | Person_ID| oo|Insurance_ID|------|Insurance|
| +----------+ .--|Company_ID | oo| _ID |
| : +------------+ .--|Detail_ID|
| : +---------+
| +-----------+ +------------+
| |Person | |Insurance |
| | _Spouse | | _Spouse | +---------+
| |-----------|1 oo|------------| |Policy |
| oo|Person_ID |-----|Insured_ | | _Spouse|
+---|Spouse_ | | Person_ID |1 oo|---------|
| | Person_ID| oo|Insurance_ID|------|Insurance|
| +-----------+ .--|Company_ID | oo| _ID |
| : +------------+ .--|Detail_ID|
| +------------+ : +---------+
| |Insurance |
| | _Client | +------------+
| oo|------------| |Policy |
'---|Insured_ | | _Client |
| Person_ID |1 oo|------------|
oo|Insurance_ID|-----|Insurance_ID|oo
.---|Company_ID | |Detail_ID |---.
| +------------+ +------------+ |
| |
| +----------+ +---------+ |
| |Company | |Detail | |
| 1|----------| |---------|1 |
'---|Company_ID| |Detail_ID|----'
+----------+ +---------+

Note: oo = "many" end of relationship
1 = "one" end of relationship

Example Tables follow, in alphabetical order. (In my own databases, I
define Lookup properties on all my foreign keys, to make the datasheets
easier to read. In this case I thought it might be better to show you
the raw key values stored in the Tables, so you could observe how they
link together.)

Each of the Tables probably needs additional fields.

This Table, as Tom Ellison suggested, lets you list each address just
once, to be shared by everyone living at that address. (Instead of
entering "836 SE 127th St." into each of several records, possibly
mistyping it, you select the number 12245183 from a list. Actually, the
user shouldn't see the number, but rather a list of addresses in a combo
box on a Form, with the option of entering a new address.)

[Address] Table Datasheet view:

Address_ID Address
---------- ------------------
12245183 836 SE 127th St.
155959414 666 Banshee Blvd.
173127785 1234 E. Main St.

The next Table does the same with insurance company names.

[Company] Table Datasheet view:

Company_ID Name
----------- -------
-1459391524 Everest
-78983385 Acme

This Table lists various perils that might be covered; each may appear
in multiple policies.

[Detail] Table Datasheet view:

Detail_ID Type
----------- ----------
940690545 Earthquake
443127896 Fire
-450676459 Flooding
-536392113 Hail
-1066305205 Hair loss
-2146535970 Lightning
-2001082702 Loss of nest
2001901126 Meteor strike
-492063696 Tooth decay
-512829575 Volcano

In the next Table, I couldn't determine what [Policy Owner] might be.
You can determine the contact person by following links. For example,
in a child's policy, [Insured_Person_ID] identifies the child, and that
record's [Parent_Person_ID] link identifies the client who owns the
policy. Or, this field could be a direct link to the owner's record,
but it's usually good to avoid storing redundant information in a Table,
because maintaining it takes unnecessary extra work.

The lines were a bit too wide for email, so I split the Table's listing,
repeating the primary key, [Insurance_ID], to help keep track of the
records. (It appears only once in each record.)

[Insurance] Table Datasheet view:

Insurance_ID Policy Company_ID Policy Life
Number Owner Insured
------------ ------ ----------- ------- -------
-1041598040 882-22 -1459391524 B. Bird No
-543328349 179-33 -78983385 Snuffy No
-242941295 600-28 -78983385 Ernie No
-9603188 882-57 -1459391524 Maria No
852585834 772-63 -1459391524 Oscar Yes
998160080 885-42 -1459391524 Bert No
2058552749 816-80 -1459391524 No

Insurance_ID Insured_Person_ID
------------ -----------------
-1041598040 8940911
-543328349 -1071790618
-242941295 -1737207726
-9603188 -1987616873
852585834 -2135742055
998160080 -2135742055
2058552749 453834372

The next Table includes records for all contacts, spouses, and children,
with links to other information such as address, parent, or spouse.

[Person] Table Datasheet view:

Person_ID Title First Middle Surname Sex
Name Name
----------- ----- ------- ------ ------- ---
-2135742055 Ms Mary A Jones F
-1987616873 Ms June Jones F
-1737207726 Colleen Smith F
-1071790618 Dr George Jones M
8940911 Tracy Q Jones F
453834372 Billy Jones M
1942089013 Mr Sydney Smith M

Person_ID Spouse_ Parent_ IsCon Address_ID
Person_ID Person_ID tact?
----------- ----------- ----------- ----- ----------
-2135742055 -2135742055 0 Yes 155959414
-1987616873 -2135742055 0 No 173127785
-1737207726 0 1942089013 No 12245183
-1071790618 0 Yes 173127785
8940911 0 -2135742055 No 173127785
453834372 0 -2135742055 No 173127785
1942089013 0 0 Yes 12245183

The next Table contains only some links, in each record identifying some
insurance policy and some detail (in my example, the name of a covered
hazard) in that policy.

[Policy] Table Datasheet view:

Policy_ID Insurance_ID Detail_ID
----------- ------------ ------------
-1772889958 -9603188 443127896
-1286666971 -242941295 -512829575
-742185598 852585834 2001901126
-511421815 -1041598040 -536392113
-478125093 852585834 443127896
-218923041 -543328349 -450676459
1000970262 -1041598040 -2001082702
1179632046 2058552749 -1066305205
1191726976 998160080 -492063696
1407383937 998160080 940690545
1450169396 -242941295 -2146535970
1722141597 -9603188 -492063696

Now, you may well ask, what good is it? Is there a way to get a
meaningful list from these Tables full of ugly-looking numbers? Fear
not, it should be easy. For example, the next Query lists all the
hazards in all the policies covering a child of one of the contacts.

Notice that I renamed some of the references to [Person] to reflect just
what kind of person the reference intended to reflect. For example,
[Child].[First Name] actually looks up the [Person].[First Name] field,
but calling it [Child] helps us remember that we're looking at a child's
name, instead of a spouse's name.

[Q_Children's Policies] SQL:

SELECT Contact.[First Name], Contact.Surname,
Child.[First Name], Child.Sex, Address.Address,
Company.Name, Insurance.[Policy Number],
Detail.Type
FROM ((Person AS Child INNER JOIN Address
ON Child.Address_ID = Address.Address_ID)
INNER JOIN Person AS Contact
ON Child.Parent_Person_ID = Contact.Person_ID)
INNER JOIN ((Insurance INNER JOIN Company
ON Insurance.Company_ID = Company.Company_ID)
INNER JOIN (Detail INNER JOIN Policy
ON Detail.Detail_ID = Policy.Detail_ID)
ON (Insurance.Insurance_ID = Policy.Insurance_ID)
AND (Insurance.Insurance_ID = Policy.Insurance_ID)
AND (Insurance.Insurance_ID = Policy.Insurance_ID))
ON (Child.Person_ID = Insurance.Insured_Person_ID)
AND (Child.Person_ID = Insurance.Insured_Person_ID)
AND (Child.Person_ID = Insurance.Insured_Person_ID)
WHERE (((Contact.[IsContact?])=Yes))
ORDER BY Contact.Surname, Child.[First Name];

The results of running this Query look like this (but again I had to
split the output, which was too wide for the page):

[Q_Children's Policies] Query Datasheet View:

Contact. Surname Child. Sex
First Name First Name
---------- ------- ---------- ---
Mary Jones Billy M
Mary Jones Tracy F
Mary Jones Tracy F
Sydney Smith Colleen F
Sydney Smith Colleen F

Address Name Policy Type
Number
---------------- ------- ------ ----------
1234 E. Main St. Everest 816-80 Hair loss
1234 E. Main St. Everest 882-22 Loss of nest
1234 E. Main St. Everest 882-22 Hail
836 SE 127th St. Acme 600-28 Volcano
836 SE 127th St. Acme 600-28 Lightning

Please bear in mind that, though I entered these imitation data by using
Table Datasheet View, your users should use Forms to perform that
function. Using a Form will allow you to check for obvious mistakes
while a user is entering data, so you can help protect the contents of
the Tables from damage. Access provides a Wizard to help you generate a
Form, once your Query is working properly.

There is also a Report Wizard that will help you design a Report so that
you can see a list which suppresses repeating information, such as the
street addresses or the sponsor's names.

-- Vincent Johns
Please feel free to quote anything I say here.

Hello, kingnothing, I'm back...

[quoted text clipped - 70 lines]

kingnothing

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200603/1