Compare based on table values.

[Rex]

Hi

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.

I want to compare these two and state whether they are same or not. I
would like to do this on a form containing subforms.

Feel free to ask for clarification.

Thank you.

[Ed Murphy]

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.)

[Erland Sommarskog]

Rex (ra*******@gmail.com) writes:

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.

I want to compare these two and state whether they are same or not. I
would like to do this on a form containing subforms.

Don't really know where the form or the subform comes into the
picture. This is a group for a database engine. :-)

SELECT CASE WHEN EXISTS (SELECT 1
FROM tbl
WHERE member IN (@member1, @member2, @member3)
AND trackID = @trackID
GROUP BY marker
HAVING MIN(allelel) < MAX(allellel)
THEN 'There are difference'
ELSE 'All allellel are equal'
END

This is a bit of a guess, since it's not clear how you want the data.
For the future, a tip is that if you post:

o CREATE TABLE statement for your table.
o INSERT statements with sample data.
o The desired result given the sample.

Your odds for getting a tested query in respose are quite good.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx