Counting Total Participation in Years

[Guest]

I am attempting to pull out several "facts" from an access file (consisting
of 6200 records), which was created from a very large excel file. I have
found the majority of facts I am looking for (such as placing, medals won,
etc.), but one. I am attempting to find how many Games someone has
participating in over a sporting career. The problem I am having is they may
have participated in 2 or 3 events at the same games. For example.

Year Sport event name

1975 athletics 100m Joe Smith
1975 athletics 200m Joe Smith
1979 athletics 100m Joe Smith

When I do a count by name I get 3, but I am only looking to count the year,
so it should be 2 as he only participated in 2 Games.

Any way to do this in access so I don't have to go through the 6200 rows of
an excel file and delete duplicate events at the same games.

Thanks

 

[Van T. Dinh]

You can create a DISTICT Query selceting only [Name] and [Year] and then use
this Query as the DataSource for your Count Query.

You can combine all into 1 Query with SQL similar to:

SELECT [Name], Count([Year])
FROM
( SELECT DISTINCT [Name], [Year]
FROM YourTable )
GROUP BY [Name]

BTW, both [Name] and [Year] are probably bad Field names as they are
reserved word in Access (VBA).