Count number of species per site

[Liam87]

I have two tables for a monitoring database. The SURVEY table has fields [Survey_ID(PK), Site_ID, Date_Survey] and RESULTS table has [Surver_ID(FK,PK), Species(PK), Abundance]. (survey_id and species are the PK as a single species should not be recorded more than once per survey as it should have been summed up at the end of the survey.).

I have a query to give me the season and year, as the query I am working on I need to show how many sites a species occurs in, within each season of a particular year. (i.e. in winter 2012 magpies occured across 2 sites).

In the query I have Season and Survey_Year (from the query mentioned above), with criteria set to the specific month and year respectively. I then have Species and now need to add an expression field to calculate how many sites this species comes across. I tried a few ways but kept getting aggregate errors coming from the Season and Survey_Year fields.



Any pointers how to go about this would be great.

Thanks, Liam

[recyan]

If possible, post the tables with a few dummy data thrown in along with the results that you want from your query.
Also, post the sql code of the query that you currently have & the error message that is being thrown up.

Thanks

[June7]

Yes, can be more productive if able to analyse data and test queries. If you want to provide db, follow instructions at bottom of my post.

Consider building a report that uses Grouping & Sorting with aggregate calcs in group footers.

[Liam87]

Below is how the tables basically look. I'll have a play around with the report aggregation. Thanks

SurveyID	Species	Abundance
1	FrogA	10
1	FrogB	2
1	FrogD	1
2	FrogA	2

SurveyID	SiteID	Date_Survey
1	2	14/11/2012
2	4	15/11/2012

[Liam87]

I managed to solve it with a cross-tab query, concatenated the Season and Year so that the column would be the Spring2012, used species as the row and used Site, count as the value.

Cheers