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 >