How to count specific value in a table in Access query

Question

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;

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

InstructorAttendance Table:

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?

I expect it is because each record can provide a different date range criteria. Show sample of primary table data. — June7, Mar 27 '21 at 17:12
Yes, `Dcount` takes a different date range on each. What else I have to do? — Abzal Ali, Mar 27 '21 at 18:31
Actually, I would have thought inclusion of the IUID criteria would have made the date range criteria unnecessary since the IUID is unique in the posted data. Why would count be more than 1? Edit question to show sample data as well as desired output as text tables. Include table names. Also provide the complete SQL statement. — June7, Mar 27 '21 at 19:57
Data as text tables is more appreciated than images. AttnStatus does not show 1 as data, it has text "P" and "T" - which do you want? — June7, Mar 27 '21 at 23:30

score 1 · Accepted Answer · answered Mar 27 '21 at 23:35

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.

How to count specific value in a table in Access query

1 Answers1