Solved

COUNT SWITCH Query MS Access

Posted on 2012-03-13
2
515 Views
Last Modified: 2012-03-13
I am attempting to create a Query that outputs Sales Data and am having difficulty retrieving COUNT based on my Refund process.

When a Sale is made a positive amount is Inserted to DB.Table: When a Refund is made a negative amount is Inserted to DB.Table.

So COUNT() Function needs to be able to Add 1 record for Positive and Minus 1 for Negative.

Current Query that returns COUNT() on all Sales i.e. treats both pos and neg as +1 is:

SELECT DISTINCT(pay.ID), SUM(Pay.Amount), SUM(pay.Discount), pos.ItemName, COUNT(pay.ID) FROM Payment pay, Posproducts pos WHERE pay.ID = pos.ID GROUP BY pay.ID ORDER BY pos.ItemName ASC

I have attempted a SWITCH Function with
COUNT(SWITCH (Amount < 0, -1, Amount >= 0, 1)
However this is obviously wrong.

Any help would be appreciated.
FLOG51
0
Comment
Question by:FLOG51
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 37717953
try

Sum(IIF(Amount < 0,1,0)) as Refund, Sum(IIF(Amount > 0,1,0)) as Sold
0
 

Author Closing Comment

by:FLOG51
ID: 37717983
Hi capricorn1

Almost what I needed: but it gave me the syntax I was after.

SUM(IIF(Amount >= 0, 1,-1))

Did the trick perfectly.

Thanks
FLOG51
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access lists formating 8 51
GA Ribbon creator 9 65
Sharepoint list value updated via access or Excel 3 35
Access 2003 query, remove dupes 17 26
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

752 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