Can I calculate a field with 2 different filter criteria in one q

[Guest]

Hi, I have a data source table with a customer name field and various metrics
fields, I'd like to sum metric "a" based on customer name being like "abc"
and another sum of metric "a" where the customer name is NOT LIKE "abc" in
one query, but I can't seem to figure this out, but, I'm a novice at this.

Best I can figure is 2 different queries, then a 3rd query to bring the two
together.

Thanks

 

[Marshall Barton]

Hi, I have a data source table with a customer name field and various metrics
fields, I'd like to sum metric "a" based on customer name being like "abc"
and another sum of metric "a" where the customer name is NOT LIKE "abc" in
one query, but I can't seem to figure this out, but, I'm a novice at this.

Best I can figure is 2 different queries, then a 3rd query to bring the two
together.

One query is sufficient:

SELECT Sum(IIf(CustName Like "*ABC*", metricA, 0)) As X
Sum(IIf(CustName Like "*ABC*", 0, metricA)) As Y
FROM yourtable

 

[Guest]

Marsh, thanks for the help. I tried this query and get a "select statement
includes a reserved word or phrase or argument name that is misspelled or
missing, or the punctuatio is incorrect?

Do I need to put the field names in []? I tried but still receive the error

thanks

 

[Guest]

I found the reason, needed a comma betweent the two variable calculations.