T
Tim
I’m having trouble with ‘count’ in a Totals Query to be used as a basis for a
summary report for mountain goat surveys.
Three tables are involved with building the query/report (Locations, Survey,
Detection).
Locations table (information on where a survey point is) in a 1:M with the
Survey table (data on where and when a survey occurred – plus associated
data). Survey is in a 1:M with the Detection table (data on when a group of
goats was seen, including compass quadrate, number of adult and number of
young goats).
What I want to show in the report is Name of Site, Number of visits to each
site, Sum of Adults for site, Sum of Young at site, Total goats at Site
(adults + young).
The trouble I am having is getting the correct count of site visits.
An example would be for a location named Haystack. For the first year for
the study, 12 surveys were conducted at Haystack with 50 different goat
observations/detections. Using the Survey table key as the count (see SQL
below), I get 50, which is how many records there are. What I would like is
a count of unique Survey keys and not a count of the number of records. I
would like the count to show 12 (number of visits to site) not 50 (number of
groups of goats detected).
I am unsure of how to get there. Thank you for your help.
SELECT tblLocations.Site_Name, Count(tblSurvey.Survey_Key) AS
CountOfSurvey_Key, Sum(tblDetection.Num_Adults) AS SumOfNum_Adults,
Sum(tblDetection.Num_Young) AS SumOfNum_Young
FROM tblLocations INNER JOIN (tblSurvey INNER JOIN tblDetection ON
tblSurvey.Survey_Key = tblDetection.Survey_Key) ON tblLocations.Location_Key
= tblSurvey.Location_Key
GROUP BY tblLocations.Site_Name
HAVING (((tblLocations.Site_Name)="Haystack Butte (Highline Trail)"));
summary report for mountain goat surveys.
Three tables are involved with building the query/report (Locations, Survey,
Detection).
Locations table (information on where a survey point is) in a 1:M with the
Survey table (data on where and when a survey occurred – plus associated
data). Survey is in a 1:M with the Detection table (data on when a group of
goats was seen, including compass quadrate, number of adult and number of
young goats).
What I want to show in the report is Name of Site, Number of visits to each
site, Sum of Adults for site, Sum of Young at site, Total goats at Site
(adults + young).
The trouble I am having is getting the correct count of site visits.
An example would be for a location named Haystack. For the first year for
the study, 12 surveys were conducted at Haystack with 50 different goat
observations/detections. Using the Survey table key as the count (see SQL
below), I get 50, which is how many records there are. What I would like is
a count of unique Survey keys and not a count of the number of records. I
would like the count to show 12 (number of visits to site) not 50 (number of
groups of goats detected).
I am unsure of how to get there. Thank you for your help.
SELECT tblLocations.Site_Name, Count(tblSurvey.Survey_Key) AS
CountOfSurvey_Key, Sum(tblDetection.Num_Adults) AS SumOfNum_Adults,
Sum(tblDetection.Num_Young) AS SumOfNum_Young
FROM tblLocations INNER JOIN (tblSurvey INNER JOIN tblDetection ON
tblSurvey.Survey_Key = tblDetection.Survey_Key) ON tblLocations.Location_Key
= tblSurvey.Location_Key
GROUP BY tblLocations.Site_Name
HAVING (((tblLocations.Site_Name)="Haystack Butte (Highline Trail)"));