Hi,

I want to compare data in two different rows of a table. For eg. I have
a table called twins and each twin has a unique individual ID, for
instance twinA (first born twin) has an ID: 2 whereas twinB (second
born twin) has an ID: 3. both of these records belong to a particular
familyID.

Now I want to be able to compare if sex of both the twins are same or
different

The original table looks something like this:

FamilyID | individual_ID | Name | Sex
---------------------------------------------------------
000 | 02 | john | male
000 | 03 | kasey | female
222 | 02 | jack | male
222 | 03 | ryan | male

I want to have a calculated field which would look at the sex of each
twin belonging to a particular family and tell me where they are same
sex or not. so the new query would look something like this:

FamilyID | individual_ID | Name | Sex | sameSex
--------------------------------------------------------------------------------
000 | 02 | john | male | No
000 | 03 | kasey | female | No
222 | 02 | jack | male | Yes
222 | 03 | ryan | male | Yes

Thanks
Rex

SELECT tblTwin.FamilyId, tblTwin.IndividualId,
tblTwin.Name, tblTwin.Sex, tblTwin_1.Name,
tblTwin_1.Sex, tblTwin_1.IndividualId,
IIf(TblTwin.sex=tbltwin_1.sex,"Same","Different") AS Expr1
FROM tblTwin INNER JOIN tblTwin AS tblTwin_1
ON tblTwin.FamilyId = tblTwin_1.FamilyId
WHERE (((tblTwin.IndividualId)<>[tblTwin_1].[IndividualId]));

"Rex" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> I want to compare data in two different rows of a table. For eg. I have
> a table called twins and each twin has a unique individual ID, for
> instance twinA (first born twin) has an ID: 2 whereas twinB (second
> born twin) has an ID: 3. both of these records belong to a particular
> familyID.
>
> Now I want to be able to compare if sex of both the twins are same or
> different
>
> The original table looks something like this:
>
> FamilyID | individual_ID | Name | Sex
> ---------------------------------------------------------
> 000 | 02 | john | male
> 000 | 03 | kasey | female
> 222 | 02 | jack | male
> 222 | 03 | ryan | male
>
> I want to have a calculated field which would look at the sex of each
> twin belonging to a particular family and tell me where they are same
> sex or not. so the new query would look something like this:
>
> FamilyID | individual_ID | Name | Sex | sameSex
> --------------------------------------------------------------------------------
> 000 | 02 | john | male | No
> 000 | 03 | kasey | female | No
> 222 | 02 | jack | male | Yes
> 222 | 03 | ryan | male | Yes
>
> Thanks
> Rex
>

Bill, the query you have written does not work unfortunately.. Also why
do you have tblTwin_1. This is a bit of concern to me as there is only
one table "twins".


Bill Edwards wrote:
> SELECT tblTwin.FamilyId, tblTwin.IndividualId,
> tblTwin.Name, tblTwin.Sex, tblTwin_1.Name,
> tblTwin_1.Sex, tblTwin_1.IndividualId,
> IIf(TblTwin.sex=tbltwin_1.sex,"Same","Different") AS Expr1
> FROM tblTwin INNER JOIN tblTwin AS tblTwin_1
> ON tblTwin.FamilyId = tblTwin_1.FamilyId
> WHERE (((tblTwin.IndividualId)<>[tblTwin_1].[IndividualId]));
>
> "Rex" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi,
> >
> > I want to compare data in two different rows of a table. For eg. I have
> > a table called twins and each twin has a unique individual ID, for
> > instance twinA (first born twin) has an ID: 2 whereas twinB (second
> > born twin) has an ID: 3. both of these records belong to a particular
> > familyID.
> >
> > Now I want to be able to compare if sex of both the twins are same or
> > different
> >
> > The original table looks something like this:
> >
> > FamilyID | individual_ID | Name | Sex
> > ---------------------------------------------------------
> > 000 | 02 | john | male
> > 000 | 03 | kasey | female
> > 222 | 02 | jack | male
> > 222 | 03 | ryan | male
> >
> > I want to have a calculated field which would look at the sex of each
> > twin belonging to a particular family and tell me where they are same
> > sex or not. so the new query would look something like this:
> >
> > FamilyID | individual_ID | Name | Sex | sameSex
> > --------------------------------------------------------------------------------
> > 000 | 02 | john | male | No
> > 000 | 03 | kasey | female | No
> > 222 | 02 | jack | male | Yes
> > 222 | 03 | ryan | male | Yes
> >
> > Thanks
> > Rex
> >

Need a bit more clarifications:

1. How do you distinguish the multiple sets of twins if a family has more
than 1 set of twins?

2. Any triplet or quad in your database?

--
HTH
Van T. Dinh
MVP (Access)



"Rex" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> I want to compare data in two different rows of a table. For eg. I have
> a table called twins and each twin has a unique individual ID, for
> instance twinA (first born twin) has an ID: 2 whereas twinB (second
> born twin) has an ID: 3. both of these records belong to a particular
> familyID.
>
> Now I want to be able to compare if sex of both the twins are same or
> different
>
> The original table looks something like this:
>
> FamilyID | individual_ID | Name | Sex
> ---------------------------------------------------------
> 000 | 02 | john | male
> 000 | 03 | kasey | female
> 222 | 02 | jack | male
> 222 | 03 | ryan | male
>
> I want to have a calculated field which would look at the sex of each
> twin belonging to a particular family and tell me where they are same
> sex or not. so the new query would look something like this:
>
> FamilyID | individual_ID | Name | Sex | sameSex
> --------------------------------------------------------------------------------
> 000 | 02 | john | male | No
> 000 | 03 | kasey | female | No
> 222 | 02 | jack | male | Yes
> 222 | 03 | ryan | male | Yes
>
> Thanks
> Rex
>

each individual is recognized based on the individual ID so first set
of twins is given individual ID as 2 and 3 second set of twins have
individual IDs 7 and 8, first set of triplets have individual IDs as 4,
5, 6. Database does not include second set of triplets. It also does
not include quads..


Van T. Dinh wrote:
> Need a bit more clarifications:
>
> 1. How do you distinguish the multiple sets of twins if a family has more
> than 1 set of twins?
>
> 2. Any triplet or quad in your database?
>
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
>
>
> "Rex" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi,
> >
> > I want to compare data in two different rows of a table. For eg. I have
> > a table called twins and each twin has a unique individual ID, for
> > instance twinA (first born twin) has an ID: 2 whereas twinB (second
> > born twin) has an ID: 3. both of these records belong to a particular
> > familyID.
> >
> > Now I want to be able to compare if sex of both the twins are same or
> > different
> >
> > The original table looks something like this:
> >
> > FamilyID | individual_ID | Name | Sex
> > ---------------------------------------------------------
> > 000 | 02 | john | male
> > 000 | 03 | kasey | female
> > 222 | 02 | jack | male
> > 222 | 03 | ryan | male
> >
> > I want to have a calculated field which would look at the sex of each
> > twin belonging to a particular family and tell me where they are same
> > sex or not. so the new query would look something like this:
> >
> > FamilyID | individual_ID | Name | Sex | sameSex
> > --------------------------------------------------------------------------------
> > 000 | 02 | john | male | No
> > 000 | 03 | kasey | female | No
> > 222 | 02 | jack | male | Yes
> > 222 | 03 | ryan | male | Yes
> >
> > Thanks
> > Rex
> >

I know the database design is very poor.. but I cant change the design
as lot of data has already been entered in the tables..

Rex

Rex wrote:
> each individual is recognized based on the individual ID so first set
> of twins is given individual ID as 2 and 3 second set of twins have
> individual IDs 7 and 8, first set of triplets have individual IDs as 4,
> 5, 6. Database does not include second set of triplets. It also does
> not include quads..
>
>
> Van T. Dinh wrote:
> > Need a bit more clarifications:
> >
> > 1. How do you distinguish the multiple sets of twins if a family has more
> > than 1 set of twins?
> >
> > 2. Any triplet or quad in your database?
> >
> > --
> > HTH
> > Van T. Dinh
> > MVP (Access)
> >
> >
> >
> > "Rex" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > Hi,
> > >
> > > I want to compare data in two different rows of a table. For eg. I have
> > > a table called twins and each twin has a unique individual ID, for
> > > instance twinA (first born twin) has an ID: 2 whereas twinB (second
> > > born twin) has an ID: 3. both of these records belong to a particular
> > > familyID.
> > >
> > > Now I want to be able to compare if sex of both the twins are same or
> > > different
> > >
> > > The original table looks something like this:
> > >
> > > FamilyID | individual_ID | Name | Sex
> > > ---------------------------------------------------------
> > > 000 | 02 | john | male
> > > 000 | 03 | kasey | female
> > > 222 | 02 | jack | male
> > > 222 | 03 | ryan | male
> > >
> > > I want to have a calculated field which would look at the sex of each
> > > twin belonging to a particular family and tell me where they are same
> > > sex or not. so the new query would look something like this:
> > >
> > > FamilyID | individual_ID | Name | Sex | sameSex
> > > --------------------------------------------------------------------------------
> > > 000 | 02 | john | male | No
> > > 000 | 03 | kasey | female | No
> > > 222 | 02 | jack | male | Yes
> > > 222 | 03 | ryan | male | Yes
> > >
> > > Thanks
> > > Rex
> > >

Hi.. is there anyone who can solve this problem of mine..

Rex wrote:
> I know the database design is very poor.. but I cant change the design
> as lot of data has already been entered in the tables..
>
> Rex
>
> Rex wrote:
> > each individual is recognized based on the individual ID so first set
> > of twins is given individual ID as 2 and 3 second set of twins have
> > individual IDs 7 and 8, first set of triplets have individual IDs as 4,
> > 5, 6. Database does not include second set of triplets. It also does
> > not include quads..
> >
> >
> > Van T. Dinh wrote:
> > > Need a bit more clarifications:
> > >
> > > 1. How do you distinguish the multiple sets of twins if a family has more
> > > than 1 set of twins?
> > >
> > > 2. Any triplet or quad in your database?
> > >
> > > --
> > > HTH
> > > Van T. Dinh
> > > MVP (Access)
> > >
> > >
> > >
> > > "Rex" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > Hi,
> > > >
> > > > I want to compare data in two different rows of a table. For eg. I have
> > > > a table called twins and each twin has a unique individual ID, for
> > > > instance twinA (first born twin) has an ID: 2 whereas twinB (second
> > > > born twin) has an ID: 3. both of these records belong to a particular
> > > > familyID.
> > > >
> > > > Now I want to be able to compare if sex of both the twins are same or
> > > > different
> > > >
> > > > The original table looks something like this:
> > > >
> > > > FamilyID | individual_ID | Name | Sex
> > > > ---------------------------------------------------------
> > > > 000 | 02 | john | male
> > > > 000 | 03 | kasey | female
> > > > 222 | 02 | jack | male
> > > > 222 | 03 | ryan | male
> > > >
> > > > I want to have a calculated field which would look at the sex of each
> > > > twin belonging to a particular family and tell me where they are same
> > > > sex or not. so the new query would look something like this:
> > > >
> > > > FamilyID | individual_ID | Name | Sex | sameSex
> > > > --------------------------------------------------------------------------------
> > > > 000 | 02 | john | male | No
> > > > 000 | 03 | kasey | female | No
> > > > 222 | 02 | jack | male | Yes
> > > > 222 | 03 | ryan | male | Yes
> > > >
> > > > Thanks
> > > > Rex
> > > >

While it may be possible to fudge around with SQL to work out what you want,
you will encounter numerous problems later with the current Table structure
and it is much better to re-structure your Table(s) according to the
Relational Database Design Theory.

--
HTH
Van T. Dinh
MVP (Access)



"Rex" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi.. is there anyone who can solve this problem of mine..
>

I know I need to change my table structures.. for which we are
designing a new database from scrach..

For the time being is it possible for you to give me the solution of my
problem. Also where can I read about Relational Database Design Theory

cheers
Van T. Dinh wrote:
> While it may be possible to fudge around with SQL to work out what you want,
> you will encounter numerous problems later with the current Table structure
> and it is much better to re-structure your Table(s) according to the
> Relational Database Design Theory.
>
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
>
>
> "Rex" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi.. is there anyone who can solve this problem of mine..
> >

See the Microsoft Knowledge Base article:

http://support.microsoft.com/kb/283698

Forget about trying to work with current structure. I tried to do a quick
sample SQL but it is simply too messy so I gave up.

--
HTH
Van T. Dinh
MVP (Access)



"Rex" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I know I need to change my table structures.. for which we are
> designing a new database from scrach..
>
> For the time being is it possible for you to give me the solution of my
> problem. Also where can I read about Relational Database Design Theory
>
> cheers