SQL Query with condition - Count new and returning values for each ID

So here is the problem that I've been trying to solve without success for the last couple of days: I have a table which keeps track of the participation of people (identified by their unique PartcipantId) to some events (identified by their unique EventId).

I would like to write a SQL query (I'm using MS Access 2010) which returns for each event the number of returning participants (ie which have already participated in another event with a lower EventId), the number of new participants (first time they appear, if sorting by EventId) and the total number of participants for that event?

As as example:

ParticipantId | EventId
1               1
1               3
1               4
2               3
2        开发者_开发知识库       4
3               5

Would give:

EventId | New | Returning | Total
1         1     0           1
3         1     1           2
4         0     2           2
5         1     0           1

Is this even possible to begin with? Any idea on how I could do it?

Many Thanks!

You could use a subquery to determine the first event for a user. Then, Access' iif allows you to count only first events for the New column:

select  e.eventid
,       sum(iif(e.eventid = p.FirstEvent,1,0)) as [New]
,       sum(iif(e.eventid <> p.FirstEvent,1,0)) as Returning
,       count(p.participantid) as Total
from    (
        select  participantid
        ,       min(eventid) as FirstEvent
        from    Table1
        group by
                participantid
        ) as p
left join
        Table1 e
on      p.participantid = e.participantid
group by
        e.eventid