Running Totals Based on Multiple Criteria (1 Viewer)

spudracer

Here and there
Local time
Today, 08:30
Joined
Jul 1, 2008
Messages
199
I'm trying to create a query that compiles a running total of different criteria, but I'm hitting a roadblock by not adding an AND to my DCount function.

What I want the query to do is count every selection of either Present or Leave for each Department without it displaying a total of 2 for Present and 2 for Leave for each department. Running a Totals query without the Department column, I get exactly what I want, but when I add the Department column in, it groups the numbers regardless of department. I don't want to tie the values to the Departments I have listed, because users will be able to add/remove/rename the Departments to their local use.

I've searched all over and can't seem to figure this one out.

Here's the basic setup:
DepartmentPresentLeave
Dept 11
Dept 21
Dept 31
Dept 41
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:30
Joined
May 7, 2009
Messages
19,175
can you show the expected output.
 

spudracer

Here and there
Local time
Today, 08:30
Joined
Jul 1, 2008
Messages
199
can you show the expected output.

Just like the table above, is how it should show. Right now, it shows like
DepartmentPresentLeave
Dept 12
Dept 22
Dept 32
Dept 42
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:30
Joined
May 7, 2009
Messages
19,175
you must add Criteria to your DCount() function, eg:

SELECT [Department], Present: DCount("[FieldToCount]", "
", "[DepartmentField] = '" & [Department] & "' AND [AnotherField]='Present'"),
Leave: DCount("[FieldToCount]", "
", "[DepartmentField] = '" & [Department] & "' AND [AnotherField]='Leave'")

that is only sample, without knowing the structure of your table/tables I can only guess.
 

spudracer

Here and there
Local time
Today, 08:30
Joined
Jul 1, 2008
Messages
199
you must add Criteria to your DCount() function, eg:

SELECT [Department], Present: DCount("[FieldToCount]", "
", "[DepartmentField] = '" & [Department] & "' AND [AnotherField]='Present'"),
Leave: DCount("[FieldToCount]", "
", "[DepartmentField] = '" & [Department] & "' AND [AnotherField]='Leave'")

that is only sample, without knowing the structure of your table/tables I can only guess.

That gets me going in the right direction. I appreciate it!
 

spudracer

Here and there
Local time
Today, 08:30
Joined
Jul 1, 2008
Messages
199
you must add Criteria to your DCount() function, eg:

SELECT [Department], Present: DCount("[FieldToCount]", "
", "[DepartmentField] = '" & [Department] & "' AND [AnotherField]='Present'"),
Leave: DCount("[FieldToCount]", "
", "[DepartmentField] = '" & [Department] & "' AND [AnotherField]='Leave'")

that is only sample, without knowing the structure of your table/tables I can only guess.

Worked perfectly! Thanks again!
 

Users who are viewing this thread

Top Bottom