FLOG51
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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