I am having a problem with a simple database for a health club. I need to track group exercise attendance of members (not for each member, just a total number of people attended). I have 5 fields in my table (tblAttendance): Date, Time, Class, Instructor, and Attendance. I am making a parameter query that is taking values entered in a form (frmFilters). Users can enter a date range (txtStartDate, txtEndDate) filter by instructor (txtInstructor) or filter by class (txtClass).
I run into the problem when I want to just enter a date range, and display average attendance for each unique date. I already have the criteria for other fields besides the date set to:
Like nz([Forms]![frmFilters]![txtwhatever],"*") (courtesy of CCC, from this very forum.)
That way frmFilters can accept null values. When I enter a date range on frmFilters but leave all other fields blank, I get correct information, but it displays the average attendance of each unique class, with each unique instructor, on each unique date. I would like it to instead display ONLY the information that I do not leave blank in frmFilters.
So, If I just entered a date range and a value for txtInstructor, I want the average of all classes that the instructor has taught, not the average for each class on each individual date. Any thoughts?
Also, sorry I'm terribly long-winded. I am anything but a database expert, and haven't the slightest idea what I am doing. If you need any more information, let me know. Thanks in advance!