Count in totals query

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)"));
 
M

MGFoster

Tim said:
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)"));

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Possibly this:

SELECT L.Site_Name, Count(S.Survey_Key) AS
CountOfSurvey_Key, Sum(D.Num_Adults) AS SumOfNum_Adults,
Sum(D.Num_Young) AS SumOfNum_Young

FROM tblLocations As L INNER JOIN (
(SELECT Location_Key, Survey_Key FROM tblSurvey GROUP BY Location_Key,
Survey_Key) As S INNER JOIN tblDetection As D ON S.Survey_Key =
D.Survey_Key) ON L.Location_Key = S.Location_Key

WHERE L.Site_Name="Haystack Butte (Highline Trail)"

GROUP BY L.Site_Name

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSj/RbYechKqOuFEgEQIZRQCg4ds9qzGdrYhTV+mVO4jr2I/jjl0AoNTz
tInhDpyfL4WbM1yg2I8u4yvE
=CHhb
-----END PGP SIGNATURE-----
 
D

Duane Hookom

In a report, you can use the Running Sum in a group header to count groups.

Since this is a query NG, you can create a totals query without the
tblDetection and then Count Surveys by Location. Join this query to your
report's record source query so you have a count of surveys by location.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top