DCount records by engineer

[CK_One]

Hi,

I have a database that stores gas safety audits by engineers. I would like to create a query that counts the total number of 'Post Job Audits' per engineer.

I have tried using the DCount in the query to do this which shows the correct total but displays it against all engineers and not for each engineer that have post job audits. The DCount code I am using is below

Total On Job Audits: DCount("PostOnJobAudit","JobAudit","PostOnJobAudit='Post Job Audit'")

Once this is working, I would then like to use this query to display the total on job audits in the engineer details form.

Any help would be appreciated.

Craig

 

[Minty]

You shouldn't use DAnything() in a query as the query can do the counting for you.
Click the totals symbol (Epsilon) in the query builder.

Under your PostOnJobAudit put in your criteria, and select WHERE in the totals window . Under engineer select Group on. Put the PostOnJobAudit as another field and select Count.

Run the query - does it give you want you want?.

 

[CK_One]

Hi Minty,

Thanks for the quick reply.

I have tried your suggestion and I get the same result. List of all engineers with the total post job audits against each engineer.

The records that should be shown is Ali Amaan with 2 and James Lee with 1.

SQL code below

SELECT [Engineers Extended].[Engineer Name], Count(JobAudit.PostOnJobAudit) AS CountOfPostOnJobAudit
FROM JobAudit, [Engineers Extended]
WHERE (((JobAudit.PostOnJobAudit)='Post Job Audit'))
GROUP BY [Engineers Extended].[Engineer Name];

I have attached screen snippets of the query and result for reference.

Regards,

 

[NauticalGent]

I think if you change to Total line for PostOnJobAudit from "Where" to "Group By", you will get the results you are looking for.

PMFJI Minty!

 

[Minty]

Ah - you haven't joined your two tables - you need to link them by the engineer. You're getting a Cartesian result.

 

[NauticalGent]

...or you could just listen to Minty and get the right answer. Crawling back into my hole now.

 

[NauticalGent]

CK One,

How did it work out?

 

[Minty]

...or you could just listen to Minty and get the right answer. Crawling back into my hole now.

No problem at all - I frequently read a thread, post in a hurry and get both feet firmly stuck in my own mouth... Glad I'm not alone