?
Solved

Using Sum Function on SQL Query

Posted on 2008-01-30
10
Medium Priority
?
299 Views
Last Modified: 2011-10-19
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
0
Comment
Question by:gfranco
  • 5
  • 4
10 Comments
 
LVL 10

Expert Comment

by:aesmike
ID: 20781331
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.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 20781355
Try it as:

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

Expert Comment

by:Jim P.
ID: 20781372
Oh and these should probably be a period instead.

QuotesFile.[Created By Source]
QuotesFile.[Quote Status]
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:gfranco
ID: 20781485
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
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 20781658
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

0
 

Author Comment

by:gfranco
ID: 20781686
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

0
 

Author Comment

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

formating.JPG
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 1600 total points
ID: 20786909
Format(Sum(IIf(QuotesFile![Created By Source]='CSCC' And QuotesFile![Quote Status]='QC',QuotesFile! [Net Price],0)),"Currency")
0
 

Author Comment

by:gfranco
ID: 20787637
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
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 20787725
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Implementing simple internal controls in the Microsoft Access application.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question