countifs function alternative

[kobiashi]

hi all


i know the COUNTIFS function doesnt exist in access, but what would be the alternative.

i have a excel formula to lookup two criterias within 14 day period

=COUNTIFS([V At Fault],C2,[Sub System],O2,[Date],"<="&A2,[Date],">="&A2-14)

what would the access alternative be

 

[Minty]

This would normally be done in a query - please show some example data, and your expected results.

We have no idea what the field names would be from your Excel cell references.

 

[kobiashi]

Apologies

So i have a form bound to a query,

relvant fields

VehicleNumber | Date | SubSystem
V003 | 10/05/2018 | ATO
V003 | 01/05/2018 | ATO

the field i would like to apply the formula to is called "Failure Level"

This would be based on VehicleNumber and Subsystem, within a 14 day period, the Failure level would be based on how many of the events occur in the 14 day period, so if there is 3 events matching the criteria then it would be level 3

 

[Minty]

I would use a DCount on the form- there are links for the syntax in my signature. It would be something like

=DCount("VehicleNumber","YourQueryNameHere","[Date] > Date() -14 AND [SubSystem] = '" Me.SubSystem & "'" )

Using Date as a field name is a really bad idea, it's a function and reserved name in Access, change it to something like IncindentDate .

This may well be slow if you have a lot of data, in which case you may need to use a SubQuery or two or three stage query to get your result.

This information shouldn't be stored in a field by the way - you should always calculate it. That way it is always accurate, if your underlying data changes you would have to try and capture that and and update the stored value.

 

[kobiashi]

thanks for the help minty, ill take a look at the formula

the field name isnt "Date" i was just using that as an example, but thanks for the advice

 

[kobiashi]

can i not store the value in the query?

just so i can use it on another form?

 

[kobiashi]

this is my function but it keeps saying the "expression you entered contains invalid syntax"

=DCount("cboVehAtFault","Query_MCUTracker","[MCUDate] > Date()-14 AND [cboSubSystems] = '"Me.cboSubSystems & "'")

i have put this in the control source of the field

 

[Minty]

This doesn't make much sense

=DCount("cboVehAtFault",

Unless you have a field called cboVehAtFault in your query this won't work. The syntax is

DCount("[COLOR="Red"]A Field in your source table or query[/COLOR]","[COLOR="Blue"]The Query or Table Name[/COLOR]", "[COLOR="SeaGreen"]The criteria[/COLOR]"

The second problem is your criteria is missing an &

=DCount("cboVehAtFault","Query_MCUTracker","[MCUDate] > Date()-14 AND [cboSubSystems] = '"[COLOR="Red"] & [/COLOR]Me.cboSubSystems & "'")

And the general rule of thumb is don't store what you can calculate, essentially it's duplication of data, and means you have to try and keep it accurate based on any changes to any underlying data.

 

[static]

select
	distinct vehiclenumber, 
	subsystem,
	'level ' & (select sum(1) as failures from Query_MCUTracker group by vehiclenumber, subsystem) as failurelevel
from Query_MCUTracker
where MCUDate between date()-14 and date()

 

[kobiashi]

This doesn't make much sense

=DCount("cboVehAtFault",

Unless you have a field called cboVehAtFault in your query this won't work. The syntax is

DCount("[COLOR="Red"]A Field in your source table or query[/COLOR]","[COLOR="Blue"]The Query or Table Name[/COLOR]", "[COLOR="SeaGreen"]The criteria[/COLOR]"

The second problem is your criteria is missing an &

=DCount("cboVehAtFault","Query_MCUTracker","[MCUDate] > Date()-14 AND [cboSubSystems] = '"[COLOR="Red"] & [/COLOR]Me.cboSubSystems & "'")

And the general rule of thumb is don't store what you can calculate, essentially it's duplication of data, and means you have to try and keep it accurate based on any changes to any underlying data.

Hi Minty

Im still struggling with this, i've inputted the function in the control source, of the required field im trying to calculate, which is "Failure Level", but all it displays it #Name?

just for clarification, i reference the field name not the column name in the query/table?

 

[Minty]

The better route would be to use Static's query. That will give you the number in your query, and you simply set the control source to the calculated field.

 

[kobiashi]

ill take a look thanks

im still new to access, where does the query need to go?

 

[static]

Where does a query go?

The code I posted is SQL. SQL in Access is a query. So SQL would go in a query.

When you goto create a new query it will ask for the table. Click Cancel. A button in the top left will change to show SQL. click that and enter the code I gave.

It will probably not be as efficient as it could be because I have no idea what 'Query_MCUTracker' is and you probably don't need to base one query on top of another.