Count in query

  • Thread starter Thread starter Phil
  • Start date Start date
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
 
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.
 
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
 
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
 
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
 
Back
Top