[AccessD] calculation query - addition

Pedro Janssen pedro at plex.nl
Mon Dec 22 09:09:21 CST 2008


Hello A.D. and others,

now i am getting confused myself.
In Literature ForeignMatch indeed is described als: OwnMatch for any given 
pair is the same as ForeignMatch for the same pair reversed

But how is this possible that the calculation from A.D. Tejpal gives   AC = 
66,6 own      CA = 66,6 foreign
but in my example it is: 
AC = 66,6 own       CA = 50 foreign.

Anyone an idea?

Pedro






----- Original Message ----- 
From: "Pedro Janssen" <pedro at plex.nl>
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Sent: Monday, December 22, 2008 1:13 PM
Subject: Re: [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
>>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 




More information about the AccessD mailing list