If you were recording the same species more than once in different locations however, then simple aggregation would be likely to return erroneous counts of species per region, i.e. This does assume you are only recording each species once in one region and With this correctly normalized structure and your current data you would simply join the tables and group by Class, counting the numbers per Region and Location in the same way. This allows you to map a species to a region where no specific location is identified. Diagrammatically the model would be:īecause you have some species recorded only by region there must be rows in Locations with a value such as 'N/A' for each Region. North America would be a value in a row in the Regions table Virginia would be a value in a row in the Locations table. The data should be in a set of related tables as follows: This requires that all data be stored as values at column positions in rows in tables, and in no other way. A fundamental principle of the database relational model is the Information However you have made the fundamental error of 'encoding data as column headings'. With your current table a simple aggregating query as Hans describes will do it. It's ironic that a design flaw is actuallyįacilitating what you want "It's an ill wind etc." So all you need to do is the aggregation. Trying to use a crosstab query as you originally were is unnecessary as, due to its being badly structured, the data is arriving already pivoted, but not aggregated. Which, if I understand correctly, is what you are aiming at. When you specify the column name as the argument the COUNT operator acts only on rows where there is not a NULL at the column position, so this would give counts per type of the rows with a values at the NorthAmerica and Virginia column positions respectively, It's a simple aggregating queryĬOUNT(YourTable.NorthAmerica) AS NorthAmerica,
If I were doing this I would write a routine to automate the recasting of the data into a set of normalized tables each time it's imported, but if you want to stick with what you have then I don't see the problem.
0 kommentar(er)
