Solved

COUNT SWITCH Query MS Access

Posted on 2012-03-13
2
485 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
2 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

758 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now