Subquery to return distinct count

[Max]

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

 

[Stefan Hoffmann]

hi Max,

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]

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

hi Max,

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 <--

 

[steve dassin]

Hi,

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

www.beyondsql.blogspot.com

hi Max,

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 <--

 

[Bob Barrows [MVP]]

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.

 

[John Spencer]

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
'====================================================