CODEWITHSUNDEEP
sqlms-access
Rabih Zein
Rabih Zein

Reputation: 13

how to link a field from a table to match an expression in a column?

In a davg formula, in the condition section, how to match a field in a table with a formula in a query.

In the below example, I want to avg all the numbers of [dec10], given its category is matching with the [class]

My current formula which is not working is:

Davg(“[dec10]”,”prct change”,”[Category] = [Class]“)

The error I am receiving is “Microsoft Access cannot find the name [Class] you entered into the expression”

I can achieve my desired results through:

IIf([Class]=”Retail”,Davg(“[dec10]”,”prct change”,”[category] = ‘retail’ “),
IIf([Class]=”Banks”,Davg(“[dec10]”,”prct change”,”[category] = ‘banks’ “), etc…))

However I have more than 100 classes and the size will not fit in the formula.

Any idea anyone pls?

Upvotes: 0

Views: 40

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123779

My guess is that you actually want

Davg("[dec10]","prct change","[Category] = '" & [Class] & "'")

Upvotes: 1

Related Questions