COUNT SWITCH Query MS Access

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
FLOG51Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
try

Sum(IIF(Amount < 0,1,0)) as Refund, Sum(IIF(Amount > 0,1,0)) as Sold
0
 
FLOG51Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.