Query to Count only each Responsdents first entry

[coastal]

I have a list of registrations to a promotion, they are allowed to register
as many times as they want. But I need to report back on the total number of
unique registrations / entries.

Example: Joe Smith enters 5 times and Jane Doe enters 2 times and John Doe
enters 4 times. WHat query will report to me the total unique entries as "3".

 

[akphidelt]

Create a query using SELECT DISTINCT or SELECT DISTINCTROW

Take a look at this for more information

http://office.microsoft.com/en-us/access/HP010322051033.aspx

 

[Ken Sheridan]

SELECT COUNT(*) AS NumberRegistered
FROM (SELECT DISTINCT FirstName, LastName
FROM Registrations) AS R1;

Where Registrations is the name of the table and Firstname and LastName of
columns in it. However, will not give the correct result if two or more
registrants have the same name, which cannot be ruled out. A unique numeric
RegistrantID key should therefore be used, in which case the names should not
be included in the Registrations table at all as this introduces redundancy
and the possibility of inconsistent data, but in a related Registrants table
with RegistrantID as its primary key. This is referenced by the foreign key
RegistrantID column in Registrations. The query would now be:

SELECT COUNT(*) AS NumberRegistered
FROM (SELECT DISTINCT RegistrantID
FROM Registrations) AS R1;

Or you can count the rows in Registrants where at least one matching
registrant is found in Registrations:

SELECT COUNT(*) AS NumberRegistered
FROM Registrants
WHERE EXISTS
(SELECT *
FROM Registrations
WHERE Registrations.RegistrantID = Registrants.RegistrantID);

Of course, if the Registrants table includes only those who have registered
rather than all potential registrants then you simply need to count the rows
in the table:

SELECT COUNT(*) AS NumberRegistered
FROM Registrants;

Ken Sheridan
Stafford, England