Count of a text field with criteria

[BruceFrey]

Hello

I would like to have a query look for a field value of ABS in a text field called Code1 and then only give me the count of records >13 per student.

This is a student database and this is the count of their absences. My table is names tblAttendance

I have been successful in getting a plain totaling function of count to work and looking for only values that equal "ABS" but can't seem to get a criteria of > 13 to work. I have experimented with DCOUNT but don't seem to be getting anywhere because of my limited knowledge. I don't know SQL but perhaps this is where I begin.

Any help in structuring this would be greatly appreciated.

 

[DCrake]

You need to create an aggregate query that groups by student and has a Sum using the expression

Absent:IIF([Code1]="ABS"),1,0)

Sum the above column
Then in the condition row enter >13

 

[BruceFrey]

Hello David

I'm trying that but after I paste the syntax you suggest as a new field in my existing query I get an error that says "The expression you entered has a function containing the wrong number of arguments"

What do you advise?

 

[DCrake]

Ok may be something to do with the confusion between Abs() function and Absent

What do the sql for your query look like? can you paste it here.

 

[BruceFrey]

Hello

Here's what it looks like at present

SELECT tblAttendance.[FORMAL NAME], tblStudents.[GRADE LEVEL], tblStudents.Shop, tblAttendance.CODE1, Count(tblAttendance.CODE1) AS Total

FROM tblDownloadDate, tblStudents INNER JOIN tblAttendance ON tblStudents.APID = tblAttendance.APID

GROUP BY tblAttendance.[FORMAL NAME], tblStudents.[GRADE LEVEL], tblStudents.Shop, tblAttendance.CODE1

HAVING (((tblAttendance.CODE1)="ABS"));

 

[DCrake]

That looks fine can you post the offending objects from your app. in a small db so I an actually see the data.

 

[BruceFrey]

Here is a striped down version with no names etc.

 

[DCrake]

If I put >13 in the Total column it works. What is it that you think is wrong?

Can prove explicitley as you have not incuded all the fields in the query.

 

[BruceFrey]

Hello

Can you upload the test db back so I can see if there is anything different you did vs. what I did?

 

[DCrake]

SELECT tblStudents.APID, tblAttendance.CODE1, Count(tblAttendance.CODE1) AS Total
FROM tblDownloadDate, tblStudents INNER JOIN tblAttendance ON tblStudents.APID = tblAttendance.APID
GROUP BY tblStudents.APID, tblAttendance.CODE1
HAVING (((tblAttendance.CODE1)="ABS") AND ((Count(tblAttendance.CODE1))>13));

 

[BruceFrey]

Hello David

Thank you, I pasted this sql code into a new query and it works! I'm curious though, originally you had suggested that I use Absent:IIF([Code1]="ABS"),1,0) this syntax as my new field. Did you find that this was not necessary?

Again, that you for your help.

Regards
Bruce Frey

 

[Brianwarnock]

I haven't studied the all the thread because isn't it just a case of an erroneous )

Absent:IIF([Code1]="ABS"),1,0)

Brian

 

[DCrake]

The dataset you sent me only included ABS so there was no need to use it.

 

[Brianwarnock]

Hello David

Thank you, I pasted this sql code into a new query and it works! I'm curious though, originally you had suggested that I use Absent:IIF([Code1]="ABS"),1,0) this syntax as my new field. Did you find that this was not necessary?

Again, that you for your help.

Regards
Bruce Frey

There are 2 ways of doing this , the original using Sum, and the later using Count.

Brian