Working_Girl wrote:
POLICY
CLIENT #
MEMBER_LAST
MEMBER_FIRST
DEP_ NAME
DEP_DOB
I'm assuming this is one table? If so, this is an incredibly awful
structure and is more like a spreadsheet than anything. If you want to
use spreadsheets, then use Excel. If you want to use relational
databases, then you need to normalize your structure.
You need to do this before you start trying to apply SQL to your current
structure and begin learning very, very bad lessons.
How you structure your data depends on questions for which I don't know
the answers.
Can more than one member own a policy?
Can members own more multiple policies?
Can dependents be listed on multiple policies?
Can multiple dependents be listed on one policy? (from your original
post, the answer is yes)
My approach would be to have one policy table on which policy number,
policy details are listed. THIS WAY, POLICY DETAILS ARE LISTED ONCE AND
ONLY ONCE.
I would have a single "people" table with people, dependents and owners
listed. Birthday and other information could be listed (I would have a
separate table for addresses as well, BTW). Each person record would
need an identifier which we can call the client number (whether or not
the person is a dependent) as each person could be listed multiple times
as owners of policies, dependents in a policy and so on. THIS WAY, A
PERSON IS LISTED ONCE AND ONLY ONCE.
Finally, a look up table which will have multiple records per policy
with just the policy identifier listed as a field to indicate which
policy. There would also be a field which indicated "dependent" or
"owner" (you could use a code for this and have this field specified to
accept only "d" or "o" or whatever stuff you wish to have in there.
Alternately, yet another table could be devised which lists people
types, but that starts getting complicated for you, I think - this is
involved enough). Next, a person identifier field which holds only the
identifier of the person from the People table is required.
RElationships should be devised with referential integrity enforced
between the linking fields.
Thus, you'll have a single policy, 111111, listed in the policy table.
BROWN SOPHIE will be listed in the people table with a client number of
12345.
Lila, Rosa and gerry Brown will be also listed in the people table with
client numbers of 12346, 12347, 12348, for example.
In the look up table, Sophie's record for this policy will be listed as:
Policy_Identifier: 111111
People/Client_Identifier: 12345
People Type: o
The kids will be listed like this:
Policy_Identifier: 111111
People/Client_Identifier: 12346
People Type: d
Policy_Identifier: 111111
People/Client_Identifier: 12347
People Type: d
Policy_Identifier: 111111
People/Client_Identifier: 12348
People Type: d
From this sort of arrangement, you devise queries for dependents,
bringing out birthdates.
If this sounds too complex, and more than you want to do, then Access is
the wrong tool for you.
--
Tim
http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me