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
Microsoft AccessMicrosoft Development
Last Comment
FLOG51
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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