Not sure how to do a DCOUNT in a report

[dawnd3]

Hi there, I am pretty sure that I need a DCOUNT to count some records in a report. However, the report is not grouped by the feild I want to count and it is a little confusing to me. Is it possible that someone will take a look at it and give me guidance?

Thank you,

Dawn

 

[PaulBricker]

Dawn, post what it is you are trying to count. Counts can sometimes be pretty easy to perform in Reports(sometimes they can be hard also) but post what your info looks like and what you want to count.

Paul

 

[dawnd3]

Well it is kind of hard to describe, but I will try. The main table tracks students and for every student there are many LOS records (level of service). The report is run by District and for each district there are several schools, and within each school are several students. The report is grouped by school, but not by student. i.e Students names are repeated for each LOS record they have (this is how they wanted it). What I want is to be able to count how many students there are (ignoring the dups) for each school and then for the entire district. The underlying query of the report pulls in data for all of these tables. Here is the SQL for the query. (I didn't write it in SQL, I just used the query design grid.)

SELECT tblSchool.strSchoolName, tblGradeLevel.strGradeLevel, tblStudent.strDbStatus, tblStudent.strFirstName, tblStudent.strLastName, tblTherapist.strTherapistFirstName, tblTherapist.strTherapistLastName, tblServiceLevel.strTOS, tblServiceLevel.strLOS, tblServiceLevel.strNumberOfSessions, tblServiceLevel.strFrequency, tblServiceLevel.strDuration, tblDistrict.strDistrict, tblStudent.ysnPrivatePractice, tblDistrict.lngDistrictID, tblServiceLevel.dtmAssignDate, tblServiceLevel.dtmAssignEnd, tblServiceLevel.dtmEndDate, tblStudent.lngStudentID
FROM tblTherapist INNER JOIN ((tblSchool RIGHT JOIN (tblGradeLevel RIGHT JOIN (tblDistrict INNER JOIN tblStudent ON tblDistrict.lngDistrictID = tblStudent.lngDistrictOfPlacement) ON tblGradeLevel.lngGradeLevelID = tblStudent.lngGradeLevelID) ON tblSchool.lngSchoolID = tblStudent.lngSchoolID) INNER JOIN tblServiceLevel ON tblStudent.lngStudentID = tblServiceLevel.lngStudentID) ON tblTherapist.lngTherapistID = tblServiceLevel.lngTherapistID
WHERE (((tblStudent.strDbStatus)=[Active or Pending?]) AND ((tblStudent.ysnPrivatePractice)=No) AND ((tblServiceLevel.dtmAssignEnd) Is Null) AND ((tblServiceLevel.dtmEndDate) Is Null))
WITH OWNERACCESS OPTION;

Thanks a bunch,

Dawn

http://www.calliopeconsulting.net

 

[PaulBricker]

OK, so this isn't going to be an easy one. You can send me a copy of the db to pbricker@attbi.com and I'll look it over. I'm working in A2000 so it will help to know what version you are using. In the mean time you can try this.

1. Create a query using the query from above.
2. Add these fields, strSchoolName from tblSchool(and this is where you may have to experiment) either strFirstName and strLastName from tblStudent or lngStudentID from tblServiceLevel (or possibly lngStudentID from tblStudent if it's there). Whatever will give you a unique student.
3. Turn on the totals. Go to View....Totals on the menu bar or click the button with the Greek looking E
4. On the Totals line select GroupBy all the fields. Save this query (I'll call Qry1)
5. Create another query using Qry1. Add strSchoolName to the first column.
5a. If you use FirstName and LastName, create this expression on the Field line of the second column
CountofStudents:strFirstName & " " & strLastName
5b. If you use lngStudentID add this field to the second column
CountofStudents:lngStudentID
6. Turn on the Totals. GroupBy SchoolName and Count the second column.
7. Save this query as Qry2. (Use whatever names you want for the queries)

This should return a Count of students without dups in the count.

Then in your Report, put this in the SchoolName Footer.
DLookUp("CountofStudent","Qry2","[strSchoolName] = '" & [strSchoolName] & "'&quot

Try it out and let me know. If it works, then we can get the count for the Districts after that.

Paul