Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Comparing Dates of birth to find twins

Discussion in 'Information Technology' started by Working_Girl, Jul 28, 2006.

  1. Working_Girl

    Working_Girl
    Expand Collapse
    Guest

    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!
     
  2. Loading...

    Similar Threads Forum Date
    Sanatan Sikhi Comparing The Khalsa Rehat: Damdami, Nihang, Nanaksar, AKJ and Singh Sabha Traditions Sikh Sikhi Sikhism Oct 2, 2009
    Comparing Saints with Scholars Gurmat Vichaar Mar 20, 2008
    Sikh News BREAKING: Young Sikh Shot Dead In Jalalabad, Afghanistan . - Sikh24 News & Updates Breaking News Oct 2, 2016
    Pacific Oil slicks spotted in search for missing Malaysia Airlines plane (Live updates) Breaking News Mar 8, 2014
    India Candidates flock to Dera Sacha Sauda Breaking News Jan 17, 2012

  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On 10 May 2006 14:34:47 -0700, "Working_Girl" <sophie.coignaud@hec.ca>
    wrote:

    >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]
     
  4. Working_Girl

    Working_Girl
    Expand Collapse
    Guest

    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?
     
  5. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On 11 May 2006 15:42:10 -0700, "Working_Girl" <sophie.coignaud@hec.ca>
    wrote:

    >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]
     

Share This Page