[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