Comparing dates of birth to find twins

[Working_Girl]

Hi,

I have a database with insurance clients and their dependents (spouses
and children). We had a problem in the past with the twins and some of
them have been entered with one month difference in their dates of
birth, some of them have the same dates of births. I need to query
both cases. In the case where they are set up with one month
difference, the dates of birth has to be within 4 months of each other
and excluding the ones that are identical).

Here's my table fields:

POLICY
CLIENT #
MEMBER_LAST
MEMBER_FIRST
DEP_ NAME
DEP_DOB

POLICY CLIENT# MEMBER_LAST MEMBER_FIRST DEP_NAME DEP_DOB
111111 12345 BROWN SOPHIE LILI 2000/02/01
111111 12345 BROWN SOPHIE ROSA 2000/03/01
111111 12345 BROWN SOPHIE GERRY 2000/04/01
222222 54321 BOUCHARD BEN ANTOINE
1999/05/25
222222 54321 BOUCHARD BEN JULIE 1996/01/21
333333 44555 BAUER JACK KIM 1993/08/05
333333 44555 BAUER JACK ANDREA 1993/08/05

We have both cases here: twins set up with a difference of a month
(client # 12345) and twins with same date of bith (client # 44555). We
also have another example where the dependents are regular brothers and
sisters.

Does anyone know how to do this?

Thanks!

[Tim Marshall]

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_Identifi er: 111111
People/Client_Identifi er: 12345
People Type: o

The kids will be listed like this:

Policy_Identifi er: 111111
People/Client_Identifi er: 12346
People Type: d

Policy_Identifi er: 111111
People/Client_Identifi er: 12347
People Type: d

Policy_Identifi er: 111111
People/Client_Identifi er: 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

[Working_Girl]

This is a large dump of information that I am getting from mainframe,
that's how I'm gonna get hundreads of thousands of records (can't work
with that in excel), I know it's no good structure but this is what I
have to work with...

[pietlinden]

Is there anything stopping you from importing the raw data to a
temporary table and then parsing the data so that it's normalized?
Then you can query the data with normal SQL without making yourself
nuts.

[John Marshall, MVP]

Things for thought.

Not sure why you are using four months as a criteria. It is possible for
twins to be born in different years, but the difference in dobs should be
one day or less.

It is possible to have dependants with different mothers, so they could have
the same dob, but not be twins.

John... Visio MVP

"Working_Gi rl" <so************ *@hec.ca> wrote in message
news:11******** **************@ v46g2000cwv.goo glegroups.com.. .

Hi,

I have a database with insurance clients and their dependents (spouses
and children). We had a problem in the past with the twins and some of
them have been entered with one month difference in their dates of
birth, some of them have the same dates of births. I need to query
both cases. In the case where they are set up with one month
difference, the dates of birth has to be within 4 months of each other
and excluding the ones that are identical).

Here's my table fields:

POLICY
CLIENT #
MEMBER_LAST
MEMBER_FIRST
DEP_ NAME
DEP_DOB

POLICY CLIENT# MEMBER_LAST MEMBER_FIRST DEP_NAME DEP_DOB
111111 12345 BROWN SOPHIE LILI 2000/02/01
111111 12345 BROWN SOPHIE ROSA 2000/03/01
111111 12345 BROWN SOPHIE GERRY 2000/04/01
222222 54321 BOUCHARD BEN ANTOINE
1999/05/25
222222 54321 BOUCHARD BEN JULIE 1996/01/21
333333 44555 BAUER JACK KIM 1993/08/05
333333 44555 BAUER JACK ANDREA 1993/08/05

We have both cases here: twins set up with a difference of a month
(client # 12345) and twins with same date of bith (client # 44555). We
also have another example where the dependents are regular brothers and
sisters.

Does anyone know how to do this?

Thanks!