Filter & Count multiple criteira (1 Viewer)

Mick99

New member
Local time
Today, 04:51
Joined
Feb 19, 2016
Messages
14
I have a table that contains a number of Defect and there are a number of fields that I need to filter to give me a count, which will be the basis of a dashboard that will show annual and monthly data over the next few years. Please refer to attachment.

In short, once each record/defect has been completed the defect status is set to "Defect – Resolved". At this time I need this newly completed defect to automatically update the count of the dashboard form. I need to use the following combination of fields to filter the data that will be a count for the Jan 2020 text box, against the correct "AFE" or "Emergent" and against the correct defect category.

I tried the following as a start, but I am unable to get it to work.
=Count(IIf([tbl_Defect_List].[Month]="Jan",0)) And where([Risk_level]=[”Low”],0)

[Year]=”2020”

[Month]=”Jan”

[Risk_level]=”Low”

[Expenditure_Type]=”Emergent”

[Defect_Status] = Defect – Resolved”

Any help would be greatly appreciated.

Cheers Mick
 

Attachments

  • Screen Shot.png
    Screen Shot.png
    100.2 KB · Views: 93

theDBguy

I’m here to help
Staff member
Local time
Today, 04:51
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF!

Have you tried using Recalc on your dashboard form?
 

plog

Banishment Pending
Local time
Today, 06:51
Joined
May 11, 2011
Messages
11,611
This code is a better start:

=DCount("[Month]", "tbl_Defect_List", "[Month]='Jan' AND [Risk_level]='Low'")


DCount(https://www.techonthenet.com/access/functions/domain/dcount.php) is the way to generate counts in tables from objects not based on them.

To add criteria to it you simply follow the pattern I have done.
 

Mick99

New member
Local time
Today, 04:51
Joined
Feb 19, 2016
Messages
14
I tried the updated code but it's just returning #Name?, what you are describing is exactly what I'd like to do.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:51
Joined
Feb 19, 2002
Messages
42,970
Is the picture the result you want or the data source? It looks like a report and so it will make a poor data source. Each domain function runs a separate query so depending on what you actually want to show, you might be better off just running a totals query and letting Access calculate all the values at once in one query.
 

Users who are viewing this thread

Top Bottom