When you ask Access to Count something, it's going to count the number of rows at the lowest level. Your query joins students with classes, so you're counting classes, not students. If you want to count students, you need to break this up into a report with subreports. Put ClassDates and CompletedClasses in the subreport, put Students in the outer report, and link on ParticipantID. You can then count students independently in the outer report.
John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of sandniel Sent: Tuesday, January 31, 2006 2:20 PM To: [email protected] Subject: [ms_access] Re: Count Function Thanks for your help! This is a very simple database. The structure is as follows: Tables: Participants, Class Date, Completed Classes, Departments Relationships: Particpants (one)- Completed Classes (many) Class Dates (one) - Complted Classes (many) Paraticipants - Department (look-up table) Here's the SQL for the query: SELECT Participants.LastName, Participants.FirstName, ClassDates.ClassDescription, CompletedClasses.ClassDate, CompletedClasses.Comments, Participants.ParticipantID FROM Participants INNER JOIN (ClassDates INNER JOIN CompletedClasses ON ClassDates.ClassDate = CompletedClasses.ClassDate) ON Participants.ParticipantID = CompletedClasses.ParticipantID ORDER BY Participants.LastName, Participants.FirstName, ClassDates.ClassDescription, CompletedClasses.ClassDate; The report is grouped by student, followed by what classes they've taken. I need a count of students. Once I get it working, I'll be adding a date range of completed classes to the query, so I can track how many students that have used my services during a given period of time. Again, thanks for your help. -- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > Please explain the structure of your tables and perhaps post the SQL from > the Record Source of your report. > > John Viescas, author > "Building Microsoft Access Applications" > "Microsoft Office Access 2003 Inside Out" > "Running Microsoft Access 2000" > "SQL Queries for Mere Mortals" > http://www.viescas.com/ > > > -----Original Message----- > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf > Of sandniel > Sent: Monday, January 30, 2006 7:46 PM > To: [email protected] > Subject: [ms_access] Count Function > > I have been trying (unsuccessfully) to use the count function to count > groups. For example, the Report is grouped by student, followed by > what classes each student has taken from me. When I use the count > function, it'll count a student 4 times, if they've taken 4 classes > from me. Your help will be greatly appreciated. > > > > > > > > > Yahoo! Groups Links > Yahoo! Groups Links Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
