Counting entries in a report

RichS

Healthy software!
Local time
Today, 21:00
Joined
Apr 16, 2008
Messages
44
I have a report that uses group headers to display the results of a query. I group entries that are the same so that it only show a single occurance, but if I do a count of the entries, it includes every occurance. How do I count the unique entries?
 
does using the expression Count([fieldname]) not help

otherwise use sql's 'select distinct' in a query
then go on to count using that query
 
Thanks for the reply Swillsy!

I was using the count([Fieldname]) and that's where it seems to count all occurances. How does the select distinct work?
 
Hiya just saw this on google answers explaining it.
btw there is a COUNT DISTINCT but you cannot use this with Access:*(

Ok this question should be relatively easily for someone who knows
their SQL. It has to do with COUNT and DISTINCT I'm using Microsoft
SQL Server.

All right I have a table that looks like so:

SalesTable

SalesPerson Department Item Qty Price SaleDate
Eric Chun VideoEq 01001 2 15.00 7/24/03
Eric Chun AudioEq 15646 1 45.00 7/23/03
Fred Chin Books 87894 2 15.00 7/23/03
Eric Chu Books 15489 3 55.00 7/23/03
Ricky Lal Tools 87965 1 5.00 7/23/03
...........................

If I run the following SQL Query

SELECT DISTINCT SalesPerson From SalesTable I get

SalesPerson
Eric Chun
Fred Chin
Ricky Lal
Louis Marc
Mandy Brewer


If I run the following SQL Query

SELECT COUNT(DISTINCT SalesPerson) AS MyCount From SalesTable I get

MyCount
5
 
Ok, I seem to be making progress but we're not out of the woods yet...!

As my original query was quite complex, I produced a second query based on the first. This has narrowed down the options, but I'm still getting a couple of entries under the same client name, as the query is selecting more than just the name, and a date associated with the name is different, hence the group of fields 'selected' is still 'distinct'... So how can I get the name to be distinct and still pull through the associated fields?
 
Hopefully, I can now explain clearer how this problem is occuring...

My query has a list of uniquely named conditions, of which there are tests linked with a one-to many relationship. When I try to use the count([Condition]) function, the report is giving the total number of tests. When I run the query without the report, it is clear that it is showing the condition for each test, meaning that if a condition has more than one test associated with it, it is showing more than once, hence the count being incorrect.

I need a line in the report footer to display how many conditions there are and not how many tests.
 

Users who are viewing this thread

Back
Top Bottom