0

I am trying to report a simple 3 column query using MS Query from a single Table, The 3 columns are 'Sales Person [EXECNAME]', 'No of Cars Sold [ITEM]', 'No of Cars That had Finance on it' [FINANCECASES]. There is no field which simply shows YES for Finance, but the is a field with the Finance Value, so I was trying to use the IIF to simply add 1 for each record that had a FinanceValue that doesn't = 0. Here is my attempt but it is simply not working? Any help would be much appreciated.

SELECT t.EXECNAME, COUNT(t.ITEM), 
IIF(SUM(t.TOTALFINANCE) = 0, 0, SUM(FINANCECASES) + 1) AS FINANCECASES
FROM t.csv t 
GROUP BY t.EXECNAME

Steve

Steve Stretch
  • 23
  • 4

1 Answers1

0
SELECT t.EXECNAME, COUNT(t.ITEM),
SUM(CASE WHEN t.TOTALFINANCE > 0 THEN 1 ELSE 0 END) AS FINANCECASES
FROM t.csv t
GROUP BY t.EXECNAME
Andrew Jones
  • 1,382
  • 10
  • 26
  • Hi Andrew, Thanks for taking time to look at my problem, unfortunately as this is being run in Ms-query (used in Excel) which has not been updated by Microsoft for a while the Case clauses do not work. I thought I would give it a try anyhow but it came up with a syntax error. Cheers – Steve Stretch Nov 26 '13 at 14:29