CODEWITHSUNDEEP
sqlms-accessif-statement
AncientSwordRage
AncientSwordRage

Reputation: 7615

Summing only if a certain customer in MS Access 2003

I wanted to use a case statement (not recognised by MS Access) but this is what I have instead:

Sum(if [Time Report].Cust_id=2
                          then ([Time Report].[Hrs P1]) 
                          ELSE 0 END IF) AS [SumOfHrs P1],  
Sum(if [Time Report].Cust_id=2
                          then ([Time Report].[Hrs P2]) 
                          ELSE 0 END IF) AS [SumOfHrs P2], 
Sum(if ([Time Report].Cust_id=2 )
                          then ([Time Report].[Hrs P3]) 
                          ELSE 0 END IF) AS [SumOfHrs P3],

etc.
What I want is a new table with just the summaries of the 'Pn's for cust_id = 2 With this sort of layout

Name    P1    P2    .....
Bob     0.23  0.45

But I get the error message Syntax error (missing operator) in query expression 'query', and then it highlights the THEN part of my query.

Whats gone wrong?

P.s: Writing the sql in anything but access 2003 is not going to be possible unfortuantely.

Upvotes: 0

Views: 530

Answers (1)

Fionnuala
Fionnuala

Reputation: 91366

I reckon you want:

Sum(iif ([Time Report].Cust_id=2,[Time Report].[Hrs P1],0)) AS [SumOfHrs P1],  
Sum(iif ([Time Report].Cust_id=2,[Time Report].[Hrs P2],0)) AS [SumOfHrs P2], 
Sum(iif ([Time Report].Cust_id=2,[Time Report].[Hrs P3],0)) AS [SumOfHrs P3],

See http://office.microsoft.com/en-us/access-help/iif-function-HA001228853.aspx

Upvotes: 1

Related Questions