Count in query

P

Phil

Is there a way to add a rowsource to aquery that will conunt the number of
records in a query? For example I rum my query and each result has a row
source that shows how many people attended on that day.

I am sure i have done this before but have a major brain block on at the
moment

Thanks for any help

Phil
 
K

Klatuu

You can do that with a Totals query.
You will want to Group By the date field and Count a field that identifies
the person.
 
P

Phil

Hi Klaatu

Thanks for your response now I may be really stupid (very likely) but I
think I did as you said
SELECT Attendees.AttendeeFirstName, Attendees.AttendeeLastName,
Registration.EventID, Events.StartDate, Events.Location,
Count(Attendees.AttendeeFirstName) AS CountOfAttendeeFirstName
FROM Attendees INNER JOIN (Events INNER JOIN Registration ON Events.EventID
= Registration.EventID) ON Attendees.AttendeeID = Registration.AttendeeID
GROUP BY Attendees.AttendeeFirstName, Attendees.AttendeeLastName,
Registration.EventID, Events.StartDate, Events.Location
ORDER BY Attendees.AttendeeLastName;


CountOfAttendeeFirstName is only 1 for every record, I tired county Event ID
as well but still only get not sure what i am doing wrong

any help is greatly appreciated

Thanks

Phil
 
J

John Spencer

You cannot count the records and return all the details in the same query.

This will count all the records.
SELECT Count(*) AS CountRecords
FROM Attendees INNER JOIN
(Events INNER JOIN Registration
ON Events.EventID = Registration.EventID)
ON Attendees.AttendeeID = Registration.AttendeeID

This will return all the records
SELECT Attendees.AttendeeFirstName, Attendees.AttendeeLastName,
Registration.EventID, Events.StartDate, Events.Location,
Count(Attendees.AttendeeFirstName) AS CountOfAttendeeFirstName
FROM Attendees INNER JOIN (Events INNER JOIN Registration ON Events.EventID
= Registration.EventID) ON Attendees.AttendeeID = Registration.AttendeeID
GROUP BY Attendees.AttendeeFirstName, Attendees.AttendeeLastName,
Registration.EventID, Events.StartDate, Events.Location
ORDER BY Attendees.AttendeeLastName;

If you want a count of attendees by Event then
SELECT Registration.EventID, Count(*) AS CountOfAttendeeFirstName
FROM Attendees INNER JOIN
(Events INNER JOIN Registration
ON Events.EventID = Registration.EventID)
ON Attendees.AttendeeID = Registration.AttendeeID
GROUP BY Registration.EventID



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

Phil

Hi JOhn

Thanks for the reply, I think I was just trying to be ambitious, I am using
albert Kallal's mailmerge and needed to add a record count to the document,
looks like i may be able to do it in word instead

Thanks

Phil
 
Top