[AccessD] calculation query

Hello A.D. Tejpal,

thanks for your help. The OwnMatch works perfect. This saves me a lot of 
time.

But the Foreign Match isn't the same for the pairs, but then reversed.

When you see the example.

ForeignMatch
Species B is found in four coordinates, Species A is found in one of these = 
25%                 (OwnMatch  A-B = 33,3%)
Species C is found in four coordinates, Species A is found in two ot these = 
50%                 (OwnMatch  A-C = 66,6%)


Could you take a look again!

Thanks

Pedro







----- Original Message ----- 
From: "A.D.Tejpal" <adtp at airtelmail.in>
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Sent: Monday, December 22, 2008 6:22 AM
Subject: Re: [AccessD] calculation query


> Pedro,
>
>    Apparently, for each species, you wish to display all possible 
> combinations of pairs with all other species. For each pair, you wish to 
> display the percent match of coordinates in two columns as follows:
>
>    (a) OwnMatch - Common coordinates as percent of total coordinates of 
> first species in the pair.
>    (b) ForeignMatch - Common coordinates as percent of total coordinates 
> of second species in the pair.
>
>    This involves recursive join of semi-Cartesian type with multi-stage 
> aggregation..
>
>    Sample query Q_SP2 given below, should get you the desired results. 
> This query uses query Q_SP1 (also given below). Although the first stage 
> query Q_SP1 has all the required output (OwnMatch for any given pair is 
> the same as ForeignMatch for the same pair reversed), the second stage 
> query  Q_SP2 is meant to provide more convenient display, by showing the 
> foreign match for each pair, directly across, in a separate column.
>
>    Note:
>    T_Symp is the name of source table, having fields Species (text type) 
> and Coordinate (number type).
>
> Best wishes,
> A.D. Tejpal
> ------------
>
> Q_SP2 - (Final Query)
> (Uses query Q_SP1 given below)
> =======================================
> SELECT Q3.Species, Q3.TotCoord, Q3.SpecRef, Q3.OwnMatch, Q4.OwnMatch AS 
> ForeignMatch
> FROM Q_SP1 AS Q3 INNER JOIN Q_SP1 AS Q4 ON Q3.SpecRef = Right(Q4.SpecRef, 
> 1) & Left(Q4.SpecRef, 1)
> ORDER BY Q3.SpecRef;
> =======================================
>
> Q_SP1 - (First Stage Query)
> (Used by query Q_SP2 given above)
> =======================================
> SELECT Q1.Species, QA.TotCoord, [Q1].[Species] & [Q2].[Species] AS 
> SpecRef, 
> Round(100*(Sum(IIf([Q1].[Coordinate]=[Q2].[Coordinate],1,0))/[TotCoord]),2) 
> AS OwnMatch
> FROM [SELECT T_Symp.Species, T_Symp.Coordinate FROM T_Symp GROUP BY 
> T_Symp.Species, T_Symp.Coordinate]. AS Q2, [SELECT T_Symp.Species, 
> T_Symp.Coordinate FROM T_Symp GROUP BY T_Symp.Species, T_Symp.Coordinate]. 
> AS Q1 INNER JOIN [SELECT Q.Species, Count(Q.Coordinate) AS TotCoord FROM 
> (SELECT T_Symp.Species, T_Symp.Coordinate FROM T_Symp GROUP BY 
> T_Symp.Species, T_Symp.Coordinate) AS Q GROUP BY Q.Species]. AS QA ON 
> Q1.Species = QA.Species
> WHERE (((Q2.Species)<>[Q1].[Species]))
> GROUP BY Q1.Species, QA.TotCoord, [Q1].[Species] & [Q2].[Species];
> =======================================
>
>  ----- Original Message ----- 
>  From: Pedro Janssen
>  To: AccessD
>  Sent: Sunday, December 21, 2008 01:39
>  Subject: [AccessD] calculation query
>
>
>  Hello Group,
>
>  i need to calculate to forms of sympatry of some species.
>  The own-sympatry   (or accompanied species)       =  the percentages of 
> how many coordinates are the same from species A; compared to B and 
> compared to C
>  The foreign-sympatry    =  in which percentage of the coordinates where 
> species B and species C are found, is also species A found
>
>  The data that i have:
>    TblSympatry ID Species Coordinate
>        1 A 11
>        2 A 13
>        3 A 14
>        4 A 11
>        5 A 14
>        6 B 11
>        7 B 15
>        8 B 15
>        9 B 16
>        10 B 17
>        11 C 11
>        12 C 14
>        13 C 14
>        14 C 16
>        15 C 18
>
>  own-sympatry:
>  Species A is found in three different Coordinates.
>  Species B is found in one of the three coordinates = 33,3%
>  Species C is found in two of the three coordinates = 66,6%
>
>  result: own-sympatry
>
>  own        percentage
>  A-B             33,3
>  A-C             66,6
>
>  foreign-sympatry
>  Species B is found in four coordinates, Species A is found in one of 
> these = 25%
>  Species C is found in four coordinates, Species A is found in two ot 
> these = 50%
>
>  result: foreign-sympatry
>
>  foreign      percentage
>  B=A              25
>  C=A              50
>
>  A total of 25 species need to be compared which each to methods.
>
>  How can i do this in Access?
>
>  Thanks
>
>  Pedro
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>