Grouping and Counting Issue - HELP!

[scottay]

Ok, I'm driving myself crazy! I've built quite a few databases in Access and pretty much know what I'm doing, but I've hit a brick wall on what seems to me a very simple database...

I'm trying to make a database that will keep track of my investments. I'm practicing to be a day trader in the Forex market and want to put something together that will let me see how I'm doing on an average basis.

In my 'Trades Table' I have an AutoNumber, Date, TimeEnteredTrade, CurrencyPair, NumberOfLots, TimeInTrade, and ProfitLoss. With this data I'm trying to group everything by date, since I might have multiple trades per day. From there I want to know how many trades I made that day, how many were profitable (count), how many were losses (count), total $ of gains (sum), total $ of losses (sum), and Net Profit (difference of profit-losses).

I've get the group by date to work fine, I can also get a count for the total number of trades grouped by date. Where I hit the wall is trying to get it to show the total trades>0 AND keep the total number of trades. I've used 'where' and have gotten it to show me >0, but when I do that it also changes the total number of trades for that day...

HELP!!!

Thanks a million!

Scott

 

[Jon K]

Instead of counting and summing fields, you can sum expressions e.g.

Profitable (count): -Sum([ProfitLoss]>0)

Total $ of losses (sum): Sum(IIf([ProfitLoss]<0, [ProfitLoss], 0))
.

 

[scottay]

THANK YOU!!!

I knew it had to be something that simple. I was messing around with the 'expression' in the totals but kept getting error when I did it and your suggestion worked perfectly.

Thanks again, my world is a better place right now