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

J

jimd

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
 
M

Marshall Barton

jimd said:
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.
Click to expand...


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
 
J

jimd

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
 
Top