Subquery to return distinct count

I am having table with Data

Year-Trainer-Event
2007-X-A
2007-X-B
2008-X-C
2008-X-A

i want count of Trainer by year wise such that Person X count for the year
2007 would be 1 and not 2, same for next year

Please suggest the way, i tried sub query but it fails

thanx

hi Max,

Max wrote:
Year-Trainer-Event
2007-X-A
2007-X-B
2008-X-C
2008-X-A

i want count of Trainer by year wise such that Person X count for the year
2007 would be 1 and not 2, same for next year
Copy this into a new query (SQL view):

SELECT [Year], [Trainer], Count(*) AS EventCount
FROM [yourTable]
GROUP BY [Year], [Trainer]


mfG
-- stefan --

Hi Stefan

thanx for reply, but my issues remain same i want the following output

Year-Total-Trainers
2007-1
2008-1

as Trainer X is doing differnet event, i want Trainer Count and not the
event count
please suggest

"Stefan Hoffmann" wrote:

hi Max,

Max wrote:
Year-Trainer-Event
2007-X-A
2007-X-B
2008-X-C
2008-X-A

i want count of Trainer by year wise such that Person X count for the year
2007 would be 1 and not 2, same for next year
Copy this into a new query (SQL view):

SELECT [Year], [Trainer], Count(*) AS EventCount
FROM [yourTable]
GROUP BY [Year], [Trainer]


mfG
-- stefan --

Hi,

He's looking for the distinct count of Trainer. I forgot if Access has a
Count(Distinct [Trainer])

www.beyondsql.blogspot.com

"Stefan Hoffmann" wrote in message
...
hi Max,

Max wrote:
Year-Trainer-Event
2007-X-A
2007-X-B
2008-X-C
2008-X-A

i want count of Trainer by year wise such that Person X count for the
year 2007 would be 1 and not 2, same for next year
Copy this into a new query (SQL view):

SELECT [Year], [Trainer], Count(*) AS EventCount
FROM [yourTable]
GROUP BY [Year], [Trainer]


mfG
-- stefan --

Max wrote:
I am having table with Data

Year-Trainer-Event
2007-X-A
2007-X-B
2008-X-C
2008-X-A

i want count of Trainer by year wise such that Person X count for the
year 2007 would be 1 and not 2, same for next year

Please suggest the way, i tried sub query but it fails

Start by creating a query to return unique events by year and trainer:

SELECT DISTINCT [Year],Trainer,[Event from yourtable

Now either save that query, calling it something like UniqueEvents and use
it as the source for a new query:
SELECT [Year], [Trainer], Count(*) AS EventCount
FROM UniqueEvents
GROUP BY [Year], [Trainer]

Or, use it in a subquery:


SELECT [Year], [Trainer], Count(*) AS EventCount
FROM
(SELECT DISTINCT [Year],Trainer,[Event from yourtable) As q
GROUP BY [Year], [Trainer]

I would do the latter if this were the only query that needed unique events
per trainer.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

SELECT [Year], Trainer, Count(Trainer)
FROM (SELECT DISTINCT YourTable.Year, Trainer
FROM YourTable)

If your field names and table names will not allow the above structure,
you will need to use two queries to get the desired result. Table and
field names should consist of only letters, numbers and the underscore
character. Also, avoid the use of reserved words such as Year, Month,
Date, etc.

SELECT DISTINCT YourTable.Year
, Trainer
FROM YourTable

Now use that query as the source to get the distinct count
SELECT [Year], Trainer, Count(Trainer)
FROM TheSavedQuery

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


Max wrote:
I am having table with Data

Year-Trainer-Event
2007-X-A
2007-X-B
2008-X-C
2008-X-A

i want count of Trainer by year wise such that Person X count for the year
2007 would be 1 and not 2, same for next year

Please suggest the way, i tried sub query but it fails

thanx