Dcount function (1 Viewer)

[shamal]

Welcome
How can the Dcount function be used to calculate the following:
The number of students who failed in the fourth grade, Division A, in mathematics

 

[Gasman]

Use all three categories as criteria.
What they are is only known to you.

Put all the criteria into a string variable and debug.print that until you get it correct.
Then use that variable in the DCount() function.

 

[AHeyne]

If you want to use DCount, then this is only possible if all the information/fields are in the table/query you want to use as the source/domain for the DCount.

 

[ebs17]

DCount... just counts. A filter can be built into the third argument; this filter must have the same syntax as the WHERE part of an SQL statement without the WHERE keyword.

Overall, DCount is a query packaged as an access function. The best way to construct queries and perform calculations is to look at the database schema (unless you know it by heart), especially if there is more than one table to use.
Without this unknown information, you can hardly get concrete solutions from third parties.

 

[Josef P.]

? DCount("*", "StudentsTable", "not exists (select 1 from AnOtherTable where AnOtherTable.StudentID = StudentsTable.StudentID and ... ")

... I would test the Exists expression in a query.
But then you can also save this query and use it directly for DCount.

 

[MajP]

Build an aggregate query returning count of students, group by class, division, course, pass/fail.
Now you have an easy dlookup to get
number of students in any grade, any division, any course, either pass or fail

dlookup("countofstudents","qryGroupBy", "Grade = 4 And Division = 'A' and Course = 'Mathmatics' and PassFail = 'Failed')

 

[AHeyne]

? DCount("*", "StudentsTable", "not exists (select 1 from AnOtherTable where AnOtherTable.StudentID = StudentsTable.StudentID and ... ")

... I would test the Exists expression in a query.
But then you can also save this query and use it directly for DCount.

Nice, I never used a 'Select' in a domain functions condition at all. Like it!

 

[Gasman]

Nice, I never used a 'Select' in a domain functions condition at all. Like it!

Isn't the Dcount() doing just that though?

 

[AHeyne]

I meant I have never formulated it myself in a condition as Josef has shown in #5.

This allows to access more tables/queries in the condition than specified in the domain parameter.

 

[ebs17]

You can also write a correct and complete query right away, including counting (DCount), determining the maximum (DMax) or determining the first value (DLookup, DFirst), and having the result passed to a custom function such as LookupSQL.

Solved - Replace query by vba code, with dcount

Hello, I have create a query, name "Requête2" : SELECT DISTINCT tblVariables.NomBase FROM tblVariables; And In my vba code, I write : Dim lCompte As Long lCompte = DCount("*", "Requête2") This code is right, and it give me the number of different NomBase. Can I do the same with just vba code ?

www.access-programmers.co.uk

This opens up the available SQL world and you don't have to create a saved domain first.

 

[Pat Hartman]

How can the Dcount function be used to calculate the following:
The number of students who failed in the fourth grade, Division A, in mathematics

dCount() would be an inefficient way to do this assuming that you also want counts of failures in other subjects and other divisions. If you used dCount() you would need a separate expression for each other group of criteria or you would need to run the dCount() x times passing in variable criteria.

More efficient would be to create a query that groups and counts all failures.

Select FailureIndictor, Grade, Division, Subject, Count(*) From YourGradeTable

You could then make a report bound to this query. That would leave you to using the dCount(), preferably with arguments, to produce a single count at a time.

You could also create a variation of this query that produces failure counts by teacher.