Complicated Query Build...the story of my life

[turnbuk]

I have a table that shows single transactions by line and includes the columns



Card Holder Name
Charge to Facility
Amount Charged
Merchant Name
Transaction Date

The problem I have is that there are negative amounts that show up in my data. This indicates that the original charge was transferred to a different facility. The card holder name will always stay associated with these transactions and only the facility number will change. For Example:

Card Holder Name	Charge to Facility	Amount Charged	Merchant Name	Transaction Date
Smith, Joe	987654321	$100.00	The Store	11/1/2012
Smith, Joe	987654321	-$100.00	The Store	11/10/2012
Smith, Joe	123456789	$100.00	The Store	11/10/2012

So here we see the initial charge, then the transfer out, then the charge applied to the correct facility. The problem I have is that I cannot get accurate counts or amounts because of these negative numbers and transfers to different facilities. They’re not actual charges (only the first one is) and for the life of me I cannot figure how to write a query that would take this into consideration. In my head and can verbally say what I want to happen but I can’t figure out how to apply it in access.

“Group by Card Holder Name, Count the number of ALL Transactions that are greater than ZERO, Count the number of negative Transactions and Subtract that count from the count of ALL Transactions great than ZERO”

So my final product would be: There are TWO positive amounts in my example above which means my total count would be 2. But there is ONE negative amount listed so I need to subtract that from my original total count of TWO. Thus, my total count of actual transactions is ONE.

I don’t have enough access knowledge to write an SQL statement so what I’m working on accomplishing I have only tried using the basic query tools in access.

Man I hope this makes sense to somebody. I’ve been killing my brain on this one…..


~Kevin

[Dal Jeanis]

SELECT
   TT.[Card Holder Name],
   SUM(TT.[Amount Charged]) AS NetAmountCharged,
   SUM(IIF(TT.[Amount Charged]>0,1,IIF(TT.[Amount Charged]=0,0,-1))) AS NetNumberofCharges
FROM 
   MyTableName AS TT
GROUP BY [Card Holder Name]

[turnbuk]

Thanks! That worked. Just what I needed.