Chart based on query but to show distinct totals

[pjdube]

Hey guys,



OK, so I am new to this and would like some pointers if you don't mind.

I have a MS Access form which is UNBOUND, on this form it has a Date Range. When I execute it I have a chart (line chart) that populates by a certain area and the date range, however, I cannot for the life of me figure out how to select distinct values (I don't want Joe Blow to be counted twice) when I sum up the date range - I just want it to count the distinct names.

Here is the SQL of the chart I have:

SELECT [Query by Date].[Service Area], Count([Query by Date].Public) AS CountOfPublic
FROM [Query by Date]
GROUP BY [Query by Date].[Service Area];

When I type in SELECT DISTINCT at the beginning -- it does nothing. I am assuming that the DISTINCT should be right after Count, but then when I do that it comes up with an error.

Help please!

THanks.

Phil

[June7]

First you need a query that returns DISTINCT Public for each Area for the given range.

SELECT DISTINCT Public, Area FROM tablename WHERE [datefield] BETWEEN [startdate] AND [enddate];

Then use that query as source for Count.

SELECT Area, Count(*) AS CountPublic FROM query1 GROUP BY Area;

Provide sample data if you need more help.

[pjdube]

June7

Thanks a million. You hit the nail on the head. I did as you described and it worked.