Pedro Janssen
pedro at plex.nl
Mon Dec 22 09:29:17 CST 2008
Hello A.D. Tejpal and others, my confusion was based on the results of the query that A.D. Tejpal made and difference with the hand-calculations i made. I discovered the difference. My results were based on the example table. The data from this example table i put in the table T_symp in the database. But i made one typing error. Sorry for the confusion. A.D. Tejpal Thank you very much for your help. Best Wishes 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 4:09 PM Subject: Re: [AccessD] calculation query - addition > 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 >> > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >