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!

 

[John Vinson]

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.

A Self Join query will do this neatly. Create a new query, adding this
table to the query grid TWICE - if the table is named [Policies]
Access will alias the second instance as [Policies_1]. Join the two
instance by Client #.

Select the FirstName and DOB fields (and anything else you want to
see) from the two instances. On the Criteria under
[Policies_1].[FirstName] put

<> [Policies].[FirstName]

to eliminate those cases where the record finds itself.

Add a calculated field:

DaysDiff: Abs(DateDiff("d", [Policies].[DOB], [Policies_1].[DOB]))

This will return the positive difference in days between the two
birthdays. Use a criterion on this of

<32

or however many days you want to consider - given the nature of human
gestation, this should work!


John W. Vinson[MVP]

 

[Working_Girl]

Doing this gives me every possible combination of twins... I have 12
separate rows for the triplets!

On one row I have Liliane, Gertrude, Lili, on the next one I have Lili,
Gertrude, Liliane, etc... How come are they repeated?

 

[John Vinson]

Doing this gives me every possible combination of twins... I have 12
separate rows for the triplets!

On one row I have Liliane, Gertrude, Lili, on the next one I have Lili,
Gertrude, Liliane, etc... How come are they repeated?

Oops! Didn't think about multiples!

Yes, you'll get every possible sequence using the <> criterion.

Assuming you'll not be dealing with quintuplets, try using a FOUR
table join: join the table to itself four times. Join Policies to
Policies_1 using the default Inner Join; Policies_1 to Policies_2
using a Left Outer Join (select the join line and choose "show all
records in Policies_1 and matching in Policies_2"); and join
Policies_2 to Policies_3 using a Left Outer Join as well.

Rather than <> FirstName as a criterion, use

=(SELECT Min([FirstName]) FROM Policies AS X WHERE X.PolicyID =
Policies.PolicyID AND X.FirstName > Policies.FirstName)

as the criterion on Policies_1.FirstName;

=(SELECT Min([FirstName]) FROM Policies AS Y WHERE Y.PolicyID =
Policies.PolicyID AND Y.FirstName > Policies_1.FirstName)

on Policies_2;

and similarly for Policies_3.

Snarky, but this will give you one field for each child, in
alphabetical order.

John W. Vinson[MVP]