|
|
Posted by Ed Murphy on 09/10/07 16:43
Rex wrote:
> Below is a table with sample data to explain what I want to achieve.
>
> trackID member marker
> allele1
> ------- ------ --------------------------------------------------
> -----------------------------------------------------
> 4734 4577 01-D8S1179 13.5
> 4734 4577 02-D21S11 12.6
> 4734 4577 03-D7S820 2.0
> 4734 4577 04-CSF1PO 3.0
> 4734 4577 06-TH01 4.0
> 4734 4577 07-D13S317 5.0
> 4734 4577 08-D16S539 9.0
> 4734 4577 11-vWA 6.0
> 4734 4577 12-TPOX 7.0
> 4734 4577 13-D18S51 1.0
>
> 4734 4578 01-D8S1179 13.0
> 4734 4578 02-D21S11 12.6
> 4734 4578 03-D7S820 2.0
> 4734 4578 04-CSF1PO 4.0
> 4734 4578 06-TH01 3.0
> 4734 4578 07-D13S317 5.0
> 4734 4578 08-D16S539 9.0
> 4734 4578 11-vWA 7.0
> 4734 4578 12-TPOX 6.0
> 4734 4578 13-D18S51 1.0
>
> Okay I am trying to compare allele1 values for differences for two or
> three different member having the same trackID. The list of markers
> would always be same for all members belonging to a particular
> trackID.
>
> So in the above sample data. The trackID is same which is 4734. There
> are two members 4577 and 4578. The markers are same for each members
> but the corresponding allele1 values are different.
*scratches head* Oh, you mean that member 4577 has markers with values
(01-D8S1179, 02-D21S11, etc.) and member 4578 also has markers with
values (01-D8S1179, 02-D21S11, etc.).
> I want to compare these two and state whether they are same or not.
select t1.trackID,
t1.member t1_member,
t2.member t2_member,
t1.marker,
t1.allele1 t1_allele1,
t2.allele1 t2_allele1
from the_table t1
join the_table t2 on t1.trackID = t2.trackID
and t1.member < t2.member
and t1.marker = t2.marker
and t1.allele1 <> t2.allele1
This does not check for members missing one or more markers. You can
do that as follows:
select t1.trackID,
t1.member t1_member,
t2.member t2_member,
t1.marker
from the_table t1
join the_table t2 on t1.trackID = t2.trackID
and t1.member <> t2.member
where t1.marker not in (
select marker
from the_table t3
where t3.trackID = t2.trackID
and t3.member = t2.member
)
> I would like to do this on a form containing subforms.
Whut? Is this an Access thing? (Crystal Reports, my preferred
reporting layer, has reports containing subreports.)
Navigation:
[Reply to this message]
|