help with report and counting values in field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report created from a table with the following fields:
[PEST], [BENEFICIAL], [COUNTY], [NUMBER_RELEASED], [REFER#], [LATITUDE] &
[LONGITUDE]
It is a detailed report showing individual records.
The report is grouped by Pest then by Beneficial and sorted by ascending
County.
I have gotten as far as to calculate on the form the total number released
for Beneficial (species) and the total released for the Pest (sometimes more
than one Beneficial).
Now I am trying, without luck to get a count of the number of unique
Counties where releases were made.
I have tried to use count on the report but there may be 5 releases to three
different Counties and the result is 5 rather than 3.
I can't seem to acheive the results I want with a query either, cause I
don't know what I'm doing.
 
Hi Cathy,

A query like this should do it:

SELECT Count([COUNTY]) AS ReleaseCounties
FROM (
SELECT DISTINCT [COUNTY]
FROM MyTable
WHERE ([NUMBER_RELEASED] > 0)
)
;

Or create a query like this
SELECT DISTINCT [COUNTY]
FROM MyTable
WHERE ([NUMBER_RELEASED] > 0)
;

and use it in a DCount() expression.

I have a report created from a table with the following fields:
[PEST], [BENEFICIAL], [COUNTY], [NUMBER_RELEASED], [REFER#], [LATITUDE] &
[LONGITUDE]
It is a detailed report showing individual records.
The report is grouped by Pest then by Beneficial and sorted by ascending
County.
I have gotten as far as to calculate on the form the total number released
for Beneficial (species) and the total released for the Pest (sometimes more
than one Beneficial).
Now I am trying, without luck to get a count of the number of unique
Counties where releases were made.
I have tried to use count on the report but there may be 5 releases to three
different Counties and the result is 5 rather than 3.
I can't seem to acheive the results I want with a query either, cause I
don't know what I'm doing.
Click to expand...
 
Thank you John,
I tried to do this but I am so new at it, I couldn't figure it out. Could
you be more explicit? Where do I put these expressions in the query grid?
Is this the code language and structure, I have seen similar dialog in other
threads and I didn't understand what to do with the information. I don't
know anything about code.
When I make the query do I bring in only the County and Number_released
fields?
Do I need to build a new report based on this query?

John Nurick said:
Hi Cathy,

A query like this should do it:

SELECT Count([COUNTY]) AS ReleaseCounties
FROM (
SELECT DISTINCT [COUNTY]
FROM MyTable
WHERE ([NUMBER_RELEASED] > 0)
)
;

Or create a query like this
SELECT DISTINCT [COUNTY]
FROM MyTable
WHERE ([NUMBER_RELEASED] > 0)
;

and use it in a DCount() expression.

I have a report created from a table with the following fields:
[PEST], [BENEFICIAL], [COUNTY], [NUMBER_RELEASED], [REFER#], [LATITUDE] &
[LONGITUDE]
It is a detailed report showing individual records.
The report is grouped by Pest then by Beneficial and sorted by ascending
County.
I have gotten as far as to calculate on the form the total number released
for Beneficial (species) and the total released for the Pest (sometimes more
than one Beneficial).
Now I am trying, without luck to get a count of the number of unique
Counties where releases were made.
I have tried to use count on the report but there may be 5 releases to three
different Counties and the result is 5 rather than 3.
I can't seem to acheive the results I want with a query either, cause I
don't know what I'm doing.
Click to expand...
Click to expand...
 
Thank you John,
I tried to do this but I am so new at it, I couldn't figure it out. Could
you be more explicit? Where do I put these expressions in the query grid?
Is this the code language and structure, I have seen similar dialog in other
threads and I didn't understand what to do with the information. I don't
know anything about code.
Click to expand...

PMFJI - open a Query in design view; click the dropdown arrow on the
leftmost icon in the toolbar and choose "SQL". The query grid is
simply a user-friendly (more or less!) tool for constructing SQL,
which is the real language of all queries. You should be able to copy
and paste John's query into the SQL window, edit it as necessary to
use your own table and fieldnames, and then either view it in the grid
or base a Report on it directly.

John W. Vinson[MVP]
 
Thanks, John.

Cathy, if you just need to get the number of counties into the report
footer or somewhere,

1) follow John Vinson's instructions to create a query like this
SELECT DISTINCT [COUNTY]
FROM MyTable
WHERE ([NUMBER_RELEASED] > 0)
;
and save it (I'll call it qryCountOfReleaseCounties)

2) in the textbox on the report, put
=DCount("*", "qryCountOfReleaseCounties")
 
Back
Top