Hi all,
I have a puzzle I've been trying to solve for a couple of days and can't come up with a solution. I have a table with counts of bugs (Bug_Counts) and a lookup table for the bugs (tlu_Bugs). The lookup table describes the taxonomic classification (Phylum, Class, Order, Family, Genus, etc) of the bugs. I need to collapse the data in Bug_Counts so only the counts from lowest taxanomic rank per Site/Year combo is used.
For example, for a given Site/Year, I have counts for bugs that could only be identified to the Order Dytiscidae, and bugs that could be identified to the Genus Agabus, Dysticus, and Sanfilippodytes (all under Order Dysticidae). I only want the counts for Agabus, Dysticus, and Sanfilippodytes, but not for those that were only identified to the Order Dytiscidae. However, if none of the genera under Dytiscidae were found I would use the Dytiscidae count (so I can't just discount all null Genera).
I've tried coming up with some sort of aggregate query - but nothing seems to be working the way I need it to. I've attached a database with the two tables and the query that needs to be collapsed.
Thanks in advanced for your help and please let me know if I need to provide more explanations.
I have a puzzle I've been trying to solve for a couple of days and can't come up with a solution. I have a table with counts of bugs (Bug_Counts) and a lookup table for the bugs (tlu_Bugs). The lookup table describes the taxonomic classification (Phylum, Class, Order, Family, Genus, etc) of the bugs. I need to collapse the data in Bug_Counts so only the counts from lowest taxanomic rank per Site/Year combo is used.
For example, for a given Site/Year, I have counts for bugs that could only be identified to the Order Dytiscidae, and bugs that could be identified to the Genus Agabus, Dysticus, and Sanfilippodytes (all under Order Dysticidae). I only want the counts for Agabus, Dysticus, and Sanfilippodytes, but not for those that were only identified to the Order Dytiscidae. However, if none of the genera under Dytiscidae were found I would use the Dytiscidae count (so I can't just discount all null Genera).
I've tried coming up with some sort of aggregate query - but nothing seems to be working the way I need it to. I've attached a database with the two tables and the query that needs to be collapsed.
Thanks in advanced for your help and please let me know if I need to provide more explanations.
