ACCESS FEED: RE: [MS_AccessPros] Problem with Dcount

Rabu, 23 Oktober 2013

RE: [MS_AccessPros] Problem with Dcount

Orlan,

Do you actually have fields named for each day of the week? I would normalize tblStudentClass so there is a single field to store the day of the week. If you can't normalize then consider creating a normalizing union query something like:

SELECT StudentID, 2 as DOW, [Monday] as ClassNum
FROM tblStudentClass
WHERE [Monday] is not null
UNION ALL
SELECT StudentID, 3, [Tuesday]
FROM tblStudentClass
WHERE [Tuesday] is not null
UNION ALL
--- etc ---
SELECT StudentID, 6, [Friday]
FROM tblStudentClass
WHERE [Friday] is not null;

You can then create a query based on the union query like:
SELECT ClassNum, Count(*) as NumStudents
FROM quniStudentClass

Add this final query to your report's record source and join to Class Number.

Duane Hookom MVP
MS Access

________________________________
> From: orlanvj@gmail.com
>
> I have two tables, one (tblStudentClass) has student IDs and class
> numbers for what a student will take on Monday, Tuesday, etc to
> Saturday. The class is a 2 digit number, 01 to 27, formated as text.
>
> The other table (tblInstructor) has the class number (01 to 27) and the
> instructor name.
>
> I am trying to get a student count for each class by day.
>
> What I have so far is a report using tblInstructor to list the class
> number,instructor name, then using Dcount to get the number of students
> taking that class Monday, Tuesday, etc. To do this I have been trying
> the following in a textbox
>
> =Dcount(Monday,tblStudentClass, Monday=ClassID)
> =Dcount(Tuesday,tbleStudentClass, Tuesday=ClassID), etc
>
> each line would have ClassID, Instructor name, number for Monday,
> Number for Tuesday, etc
>
> I keep gettng an error. What am I doing wrong?
>
> Orlan