[AccessD] calculation query

Pedro Janssen pedro at plex.nl
Mon Dec 22 06:04:40 CST 2008


Hello Gustav,

get well soon!

Pedro



----- Original Message ----- 
From: "Gustav Brock" <Gustav at cactus.dk>
To: <accessd at databaseadvisors.com>
Sent: Monday, December 22, 2008 10:18 AM
Subject: Re: [AccessD] calculation query


> Hi A. D.
>
> Nice! When everyone else pass, A.D. kicks in.
>
> My first thought when I read the question was that this looked more like a 
> VBA task - a few loops counting, comparing and summing and dividing, done.
>
> But the flue (which has hit the country and killed 30 old and week 
> persons) has nailed me to the bed for three days which is very unusual - 
> in fact, I can't recall being that ill since childhood. I couldn't 
> concentrate on five lines of code, not to say a set of queries. I was at 
> sale for five cents ...
>
> So, expressed with some weight, I hope everyone will enjoy the Christmas 
> time in good health!
>
> /gustav
>
>
>>>> adtp at airtelmail.in 22-12-2008 06:22 >>>
> 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
> 




More information about the AccessD mailing list