sum based on criteria

  • Thread starter Thread starter Jeri
  • Start date Start date
J

Jeri

Using Access 2007, I'm having fits designing a query that seems like it
should be fairly simple. I'm trying to count the number of plant species in
different microhabitats at a site. I can get to the total number of species,
but when I try to add criteria, WHERE [speciescover]=GC, I get error
messages. Here's the SQL that works:

SELECT qry_PolygonSurvey.SurveyPolygonAuto_ID,
Count(qry_SurveyFlora.CodePlusComments) AS CountOfCodePlusComments,
qry_PolygonSurvey.Survey_ID
FROM (qry_Surveys LEFT JOIN qry_PolygonSurvey ON qry_Surveys.Survey_ID =
qry_PolygonSurvey.Survey_ID) LEFT JOIN qry_SurveyFlora ON
qry_PolygonSurvey.SurveyPolygonAuto_ID = qry_SurveyFlora.PolygonAuto_ID
GROUP BY qry_PolygonSurvey.SurveyPolygonAuto_ID, qry_PolygonSurvey.Survey_ID;

Mostly it's telling me it's too complicated. Any thoughts anyone might have
would be great.
 
First try this --
SELECT Count(qry_SurveyFlora.CodePlusComments) AS CountOfCodePlusComments
FROM (qry_Surveys LEFT JOIN qry_PolygonSurvey ON qry_Surveys.Survey_ID =
qry_PolygonSurvey.Survey_ID) LEFT JOIN qry_SurveyFlora ON
qry_PolygonSurvey.SurveyPolygonAuto_ID = qry_SurveyFlora.PolygonAuto_ID
WHERE [speciescover]="GC";

Then try adding the other stuff back in one at a time.

Remember you can not have a roll-up (Count) at the same time you list all
the is to be counted.
 
Back
Top