Using DCount

[Matt Greatorex]

Okay, nice easy one for all you experts out there.

I have a table [Training_Records] that contains the fields [Employee_ID] and [Zone].
I'm trying to use DCount to set a variable, based on the number of [Employee_ID] values that have a certain [Zone] value and have more than one record in the table i.e. trained on more than one thing).

Using a query I have no problem phrasing it correctly. Here, I'm having great difficulties, for some reason.

The closest I've gotten is:

str_Criteria = "(Count([Training_Records].[Employee_ID] >1) AND ([Training_Records].[Zone] = 'DP'))"

li_No = DCount("([Employee_ID])", "[Training_Records]", str_Criteria)

I know there should be some kind of aggregate in there, but I'm stumped.

Any ideas, as usual, much appreciated.

 

[Bat17]

I think that you need to embed another Dcount() rather than try using count SQL. Alternatley do it all with SQL
http://www.access-programmers.co.uk/forums/showpost.php?p=453715&postcount=5

HTH

Peter

 

[SammyJ]

Once you start writing VBA code it is pretty much time to leave DCount behind.

I haven't used it for a long time, but I'm fairly sure there is no way to get it to do what you want. Someone can correct me if I'm wrong.

What you need to do from here is to start using the query builder to make a query return the answer you want, and then take the SQL code into VBA. After a while the simpler queries will become second nature and you won't even use the query builder.

Anyway, to do this you're going to need a HAVING clause, which I think counts your DCount function out straight away.

SELECT Employee_ID FROM Training_Records WHERE Zone = 1 GROUP BY Employee_ID HAVING Count(Employee_ID) > 1

This should give you a query with a list of Employee_IDs from that Zone with multiple records. So we're halfway there.

Three options from here.

Firstly you could save this as a saved query if it is easy for you to source the zone value you want, and then use DCount on it.

The next is to use this to open a recordset, and then count the records in it.

The third is to use this as a subquery in an outer query to perform the count in one SQL string:

SELECT Count(*) FROM Training_Records WHERE Employee_ID In
(SELECT Employee_ID FROM Training_Records WHERE Zone = 1 GROUP BY Employee_ID HAVING Count(Employee_ID) > 1)

You'll still need to run the query, but it will have only one record with one field, being your count.

I can elaborate on any of these if you like.

Sam.

 

[Matt Greatorex]

Thanks, both, for the suggestions.

I did get it working, about an hour ago.

I used a SQL query almost identical to the one suggested by SammyJ to place the result in a temporary table, and set my variable from that, using DCount.

I'm sure there's a neater way of doing it, but this works perfectly, even if it did take some working out.

 

[SammyJ]

This forum could use a flag that only the poster could set to say 'Solved' so that people don't write replies when they're not needed! Seen that on other forums.

Glad you got it going.

Bad programming this, but you can do it in one line if you want:

intCount = CurrentDB.OpenRecordset("SELECT Count(*) FROM Training_Records WHERE Employee_ID In
(SELECT Employee_ID FROM Training_Records WHERE Zone = 1 GROUP BY Employee_ID HAVING Count(Employee_ID) > 1)")(0)

Sam.