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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.