Search code examples
ms-accessdcount

How to count specific value in a table in Access query

I'm using Dcount on a query column to get how many 1 from another table. I have given the current query column value in the parameter of Dcount as below.

DCount("AttnStatus","InstructorAttendance","AttnStatus=1 And AttnDate>=#" & [FD] & "# And AttnDate<=#" & [LD] & "# And IUID=" & [InstructorUtilization].[IUID])

Sql:

SELECT qryAttendance_Crosstab.IUID, PL.PL, SubPL.SubPL, qryAttendance_Crosstab.MN, 
Instructor.InstructorName, InstructorRole.InstructorRole, Country.Country, 
qryAttendance_Crosstab.[1], qryAttendance_Crosstab.[2], qryAttendance_Crosstab.[3], 
qryAttendance_Crosstab.[4], qryAttendance_Crosstab.[5], qryAttendance_Crosstab.[6], 
qryAttendance_Crosstab.[7], qryAttendance_Crosstab.[8], qryAttendance_Crosstab.[9], 
qryAttendance_Crosstab.[10], qryAttendance_Crosstab.[11], qryAttendance_Crosstab.[12], 
qryAttendance_Crosstab.[13], qryAttendance_Crosstab.[14], qryAttendance_Crosstab.[15], 
qryAttendance_Crosstab.[16], qryAttendance_Crosstab.[17], qryAttendance_Crosstab.[18], 
qryAttendance_Crosstab.[19], qryAttendance_Crosstab.[20], qryAttendance_Crosstab.[21], 
qryAttendance_Crosstab.[22], qryAttendance_Crosstab.[23], qryAttendance_Crosstab.[24], 
qryAttendance_Crosstab.[25], qryAttendance_Crosstab.[26], qryAttendance_Crosstab.[27], 
qryAttendance_Crosstab.[28], qryAttendance_Crosstab.[29], qryAttendance_Crosstab.[30], 
qryAttendance_Crosstab.[31], qryAttendance_Crosstab.[Total Days], 
IIf(IsNumeric(Nz([Total Days],0)/(DateDiff("d",[MN],DateSerial(Year([MN]),Month([MN])+1,1)-1)-([Days]-1))),Nz([Total Days],0)/(DateDiff("d",[MN],DateSerial(Year([MN]),Month([MN])+1,1)-1)-([Days]-1)),0) AS Availability, 
DateSerial(Year([MN]),Month([MN]),1) AS FD, 
DateSerial(Year([MN]),Month([MN])+1,1)-1 AS LD, 
DCount("AttnStatus","InstructorAttendance","AttnStatus=1 And AttnDate>=#" & [FD] & "# And AttnDate<=#" & [LD] & "# And IUID=" & [InstructorUtilization].[IUID]) AS Training
FROM InstructorRole INNER JOIN 
((Country INNER JOIN Instructor ON Country.CountryID = Instructor.Country) INNER JOIN ((SubPL INNER JOIN (PL INNER JOIN (qryAttendance_Crosstab RIGHT JOIN InstructorUtilization ON qryAttendance_Crosstab.IUID = InstructorUtilization.IUID) ON PL.PLID = InstructorUtilization.PL) ON SubPL.SubPLID = InstructorUtilization.SubPL) LEFT JOIN WeekendAndHolidays ON qryAttendance_Crosstab.MN = WeekendAndHolidays.MonthDay) ON Instructor.InstructorID = InstructorUtilization.InstructorID) ON InstructorRole.IRID = Instructor.InstructorRole
ORDER BY PL.PL, SubPL.SubPL, qryAttendance_Crosstab.MN, 
Instructor.InstructorName;

enter image description here

The structure of the table from which I am trying to retrieve information using ‌Dcount is given below.

InstructorAttendance Table: enter image description here

Returning the correct value in the first row. But the problem is, the count value in the next row has gradually increased, not giving the right return.

In what way do I actually get the right result?

Solution

  • Consider:

    SELECT qryAttendance_Crosstab.*, PL.PL, SubPL.SubPL, InstructorName, InstructorRole, Country.Country, 
DCount("*", "InstructorAttendance", "IUID=" & [IUID] & " AND AttnStatus=1 
       AND Format([AttnDate],'yyyymm')=" & Format([Month],"yyyymm")) AS Training
...

    Or instead of DCount(), build an aggregate query.

    SELECT IUID, Format([AttnDate],"yyyymm") AS AttYYYYMM, Count(*) AS CountStatus
FROM InstructorAttendance
WHERE AttnStatus=1
GROUP BY IUID, Format([AttnDate],"yyyymm");

    Calculate a YYYYMM field in crosstab query. Join aggregate query to crosstab query with compound link on IUID and YYYYMM fields.

    AttendanceDate shown as international format. Be aware of issues associated with international date, review http://allenbrowne.com/ser-36.html

    Advise not to duplicate names for tables and fields. For example, Countries would be better as table name and Country as field name and better not to use IUID in multiple tables.