473,326 Members | 2,196 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Comparing dates of birth to find twins

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!

May 10 '06 #1
4 2612
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
May 10 '06 #2
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...

May 11 '06 #3
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.

May 15 '06 #4
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_Girl" <so*************@hec.ca> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.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!

May 15 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Thomas W | last post by:
I'm developing a web-application where the user sometimes has to enter dates in plain text, allthough a format may be provided to give clues. On the server side this piece of text has to be parsed...
4
by: J P Singh | last post by:
Hi All I am trying to query a database with a combination of surname and date of birth but it is giving me wrong results in certain conditions. It is the mm/dd/yyyy and dd/mm/yyyy stuff that...
4
by: F | last post by:
Hi I have posted the question few days back about problem in inserting the dates in SQL server and thankful to them who replied. That was solved and this is a nice solution....
4
by: Richard Hollenbeck | last post by:
I'm trying to write some code that will convert any of the most popular standard date formats twice in to something like "dd Mmm yyyy" (i.e. 08 Jan 1908) and compare the first with the second and...
26
by: Frank | last post by:
For my website i would like to display the age of my son in years, months, days and hours. For now i manage to get a result for totals. Like the total number of days. This is the beginning: ...
52
by: Andy Dingley | last post by:
I'm using this at present: <p title="Publication date" ></p> Works fine on screen, but Fangs/Jaws just reads it as "left bracket twenty-eight slash zero slash two thousand five fifteen colon...
1
by: kgatchell2001 | last post by:
Hi, I am a beginner working on a database to manage clients, payroll, station assignments, etc. I have created a query which calculates age based on subtracting the date of birth from the current...
2
by: Duppypog | last post by:
I'm trying to compare a date stored in a database with today's date using an If statement, but it's not returning true. Example, value in database is 11/5/2003 with today being 11/6/2003. Can...
5
by: romanisa | last post by:
Hii friends I need to calculate the age median from the given dates of birth in an employee database in MS SQl. Please help... Roma
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.