asked on

Using Sum Function on SQL Query

I am using 1 table "QuotesFile" this table have a column net price, i am including this new column

"CSCC-O-V" which will contain the sum of all field where condition is true, see attached code.

I got this error

My original query is working fine, see orginal code attached

CSCC-O-V: Sum(Net Price(IIf(QuotesFile![Created By Source]='CSCC' And QuotesFile![Quote Status]='QC')))
 
Original query
 
SELECT UCase([Created By User Name]) & " - " & [Created By User Type] AS Name, Sum(IIf([Created By Source]="CSCC",1,0)) AS [CSCC-Q], Sum(IIf(QuotesFile![Created By Source]='CSCC' And QuotesFile![Quote Status]='QC',1,0)) AS [CSCC-O], Sum(IIf([Created By Source]="IQT",1,0)) AS [IQT-Q], Sum(IIf(QuotesFile![Created By Source]='IQT' And QuotesFile![Quote Status]='QC',1,0)) AS [IQT-O], Sum(IIf([Created By Source]="SCC",1,0)) AS [SCC-Q], Sum(IIf(QuotesFile![Created By Source]='SCC' And QuotesFile![Quote Status]='QC',1,0)) AS [SCC-O]
FROM QuotesFile
WHERE (((QuotesFile.[Created By User Partner])=[Forms]![frmReports]![cboPartner]) AND ((QuotesFile.[Creation Date]) Between [Forms]![frmReports]![txtStartDate] And [Forms]![frmReports]![txtEndDate]))
GROUP BY UCase([Created By User Name]) & " - " & [Created By User Type], LCase([Created By User]) & " - " & [Created By User Type];

Open in new window

error.JPG

aesmike

your IIF expression doesn't make sense to Access.
Your IIF needs 3 arguements:
1. Condition, currently QuotesFile![Created By Source]='CSCC' And QuotesFile![Quote Status]='QC'
2. Value to return if true. Currently, nothing
3. Value to return if false. Currently, nothing
You are missing the 2nd and 3rd arguements.

Jim P.

Try it as:

CSCC-O-V: Sum(IIf(QuotesFile![Created By Source]='CSCC' And QuotesFile![Quote Status]='QC'), [Net Price],0)

Jim P.

Oh and these should probably be a period instead.

QuotesFile.[Created By Source]
QuotesFile.[Quote Status]

gfranco

ASKER

I tried using your function and also this
CSCC-O-V: Sum(IIf(QuotesFile![Created By Source]='CSCC' And QuotesFile![Quote Status]='QC'),QuotesFile! [Net Price],0)
I just need to be sum net price for this condition. thanks

But is not working i got an error see file attached

error.JPG

Jim P.

I'm assuming [Net Price] is a field in a table/query. (I won't discuss spaces in table and field names.)

I couldn't read the error message in your second screen shot. It's just pixelated.

Try it as:

CSCC-O-V: Sum(IIf(QuotesFile.[Created By Source]='CSCC' And QuotesFile.[Quote Status]='QC',QuotesFile.[Net Price],0))

Open in new window

gfranco

ASKER

No, It was working with !, because it is the reference to the field.
As i told you before, my originally query is working Perfect (see attached)

I just need to include another field that sum the field Net price where the condition is met, but when I used this
CSCC-O-V: Sum(IIf(QuotesFile![Created By Source]='CSCC' And QuotesFile![Quote Status]='QC'),QuotesFile! [Net Price],0) I got the same error, that I posted.

Thanks

SELECT UCase([Created By User Name]) & " - " & [Created By User Type] AS Name, 
Sum(IIf([Created By Source]="CSCC",1,0)) AS [CSCC-Q], 
Sum(IIf(QuotesFile![Created By Source]='CSCC' And QuotesFile![Quote Status]='QC',1,0)) AS [CSCC-O], 
Sum(IIf([Created By Source]="IQT",1,0)) AS [IQT-Q], 
Sum(IIf(QuotesFile![Created By Source]='IQT' And QuotesFile![Quote Status]='QC',1,0)) AS [IQT-O], 
Sum(IIf([Created By Source]="SCC",1,0)) AS [SCC-Q], 
Sum(IIf(QuotesFile![Created By Source]='SCC' And QuotesFile![Quote Status]='QC',1,0)) AS [SCC-O]
FROM QuotesFile
WHERE (((QuotesFile.[Created By User Partner])=Forms!frmReports!cboPartner) And 
((QuotesFile.[Creation Date]) Between Forms!frmReports!txtStartDate And Forms!frmReports!txtEndDate))
GROUP BY UCase([Created By User Name]) & " - " & [Created By User Type], LCase([Created By User]) & " - " & [Created By User Type];

Open in new window

gfranco

ASKER

It seems working, so let me know How can I format to a currency number?
$ 99,999,00

formating.JPG

ASKER CERTIFIED SOLUTION

Jim P.

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gfranco

ASKER

Thanks, what is the reason when I got the format currency through design panel, i did not see the sentence on the sql query as you show me.

This is my solution, thanks

Jim P.

Glad to be of assistance. May all your days get brighter and brighter.