Dig a little deeper

Whilst useful, our query doesn't really tell us too much more than the Trades form or even the Trades table. So it's time to put some of the more complicated querying techniques into practice.

Open a new query in design mode from the 'Create' ribbon, and select our qryTradesBase from the list on the 'Query Tab' and double click each field with the exception of 'TDate','Event' and 'Strategy' so those items appear on the bottom of the designer. One thing to note when working with the query designer is that until the query has been saved there's not a lot that we can usefully do. So let's click 'Save' and call this query 'qryTradesBySport'.

We are going to use what are known as aggregate functions in this query and I'm struggling with how best to get it down on 'paper' so it makes sense to a novice.  You might have to play around with this a bit to get what I mean... Firstly we need to select the 'Totals' button at the top right of the 'Design' ribbon. On clicking this you will see that a new 'Total' row appears on the designer, and the words 'Group By' appear under each field / table name. If you run the query at this point all you'll see is the trades, filtered by date, identical (except for the fields that we de-selected) to the results of qryTradesBase. By the way, notice that because our new query is based on the qryTradesBase query that we don't need to replicate the date selection process - neat eh?

Select the 'Group By' drop down on the TradeID field, and from the list select 'Count'. Leave Sport as 'Group By' and for the remaining fields select 'Sum' from the drop down...to leave something like this...

Running the query will give you something like this:

Notice how the field names of all fields bar 'Sport' have changed to reflect the aggregate calculation being performed. Save the query again. Let's enhance it further by adding the 'Stakes' and 'PL' fields from qryTradesBase again, and changing from 'Group By' to 'Avg' on both. You can probably guess that the query now also calculates the Average Stakes, and Average PL, by Sport. Add 'Stakes' once more, and 'PL' twice more. Change the new 'Stakes' and one of the 'PL' from 'Group by' to 'Max' and the other 'PL' to 'Min'. The query will now show our maximum amount staked per sport, and the maximm wins and losses for each as well:

This is also a good time to make sure that the data is presented neatly and in some kind of order. Go to the Sport column and select 'Sort' / 'Ascending' so that your sports are summarised in alphabetical order.

I hope you've been a bit more inventive than me in your dummy data - I seem to have a lot of mins and maxes at exactly the same figure because I've only entered one trade for that sport! The football row gives you the idea, though. Save your query, and go back into design mode as it's now time to add a couple of calculations using our old friend the 'Build' dialog...

First we want to calculate our Return on Investment, so lets go to the first vacant field in the design grid and type 'ROI:' - again ensure you include the colon... and then using the fields, you want to divide the PL by the Stakes, so you should end up with: ROI: [SumOfPL]/[SumOfStakes]. ( By the way I neglected to mention before that when you use the Build dialog and enter your field name as soon as you double click the field you want in your calculation a pesky <<Expr>> appears; you need to delete this.. i've no idea how to shortcut this - perhaps an Access expert can advise?  ). Select 'Percent' on the 'Format' tab of the property window.

Next we want to calculate our Strike Rate. This is simply winning trades / total trades. Thinking back to when we made qryTradesBase you'll remember adding the 'Winners' field, which is calculated to drop a 1 in if the trade won, and a 0 if it lost. This enables us to count the winning trades by using the 'Sum' aggregate function, as any 0's just get summed as 0 if you follow what I mean. By selecting 'Count' as the aggregate function for TradeID we can count the total number of games traded... if we used 'Count' on 'Winners' it would count all winners and all losers! You can probably now guess that to get what we want with this calculated field, we need to end up with this in the Build dialog: SR:[SumOfWinners]/[CountOfTradeID]. Once more, select 'Percent' as the format.

If you now run the query, something strange will happen. In addition to asking for a start and end date, Access will now ask you to enter 'SumofPL', 'SumofStakes'.'SumofWinners' and 'CountofTradeID'. This is not the desired outcome! The reason is that if we look at the designer we have still got 'Group by' on the columns in question in the total row. Access therefore assumes we are supplying our own data to run the query. The fix is simple...go to  'Group by' (the two new fields only!!) and change it to 'Expression'. This simply tells Access that the field is a calculated field rather than a parameter field. If you run it again you should get something like this:

Hopefully you can now see how relatively simple it is to extract some useful data from the three tables we created and present it in a fairly intuitive manner. In the next session we'll explore how to enhance the presentation of this data, and will end up with something like this:

Until next time....