How might you take this further? Some ideas...

The simple little DB we have constructed will, I think you'll agree, do at least as good a job of showing our strengths and weaknesses as most spreadsheet driven record keeping solutions. The bulk of the hard work is already done, come what may! There are, however, any number of relatively simple things that can be done to enhance the task, and to enhance the presentation of our records. The purpose of this post is really to act as a sort of thinktank for you, so you can experiment further and personalise your database.

Let's say you're developing a killer strat or two, but are paper trading it. You obvioulsy would want to record the results, but wouldn't want the stakes and profit / loss to appear in your 'proper' trading record. On a spreadsheet you'd probably do this on a separate sheet, but with the database approach it's a doddle to incorporate such a feature whilst changing very little, and using the existing file structure. Simply insert a new field in the Trades table, with the name Paper or similar and make the datatype 'Yes/No' with 'No' as the default value. It's then simply a case of dropping the new field onto the Trades Form and incorporating it in the qryTradesBase - as another parameter as we did with the dates. Nothing else need be changed!

It's all very well being able to limit the selection of trades by date, but unfortunately, as it stands at the moment, there is no way of incorporating the chosen dates into the title bar of the reports produced. As far as I am aware there is no way of capturing such information from within the main Access application itself, but with a little bit of Visual Basic for Applications you might come up with a control form similar to the one I've mocked up below:

The code to make this work is actually surprisingly easy to develop, and again a little bit of reading around the subject and trial and error is the way to go.

You might want to explore a deeper nesting of information. If you trade predominately one sport, and, unsurprisingly, football is a good example, you might like to see how various strategies perform in different leagues, or in friendlies, cup games or tournaments. With a little bit of imagination most things are possible, and whilst it can be extremely frustrating, working out how to do something new is actually quite good fun and I'm sure is also good for your karma!

Getting it all on (virtual) paper

At the moment our trading record is presented in what might best be described as a utilitarian format, so the next, and last, task for this stage of our trading database development is to get the data into a format which makes reading it and drawing conclusions from it more intuitive and visually pleasing. As you would probably expect by now this is not incredibly difficult due to the highly graphical nature of Access.

Ensuring that 'qryTradesBySport' is highlighted (but not opened) in the All Objects navigation  panel, select the Create ribbon, and 'Report Wizard'. The dialog box that pops up lists the fields in the query. We will use all of them so you could just click the '>>' button and have all the fields move over onto the right side of the screen. The problem with so doing, and why I would recommend selecting the fields one at a time, is that the report will then display the fields in the order they appear from top to bottom. You could sort them, but if you select them one at a time it's easier to control the order in which they appear on the finished page.

To my mind the best way to display the query is with the games traded, followed by the stake information, the P/L information and then ROI and Strike Rate - all grouped by Sport - but arrange it as you see fit. When you have selected all fields and ordered them as you want them to appear click next - you will be asked if you want to group the records. The answer, of course, is that we do - by Sport. After selecting Sport as the group you should then be looking as something similar to this:

Clicking next brings you to the Sort screen - assuming you remembered to sort alphabetically by Sport when designing your query I'd ignore this screen  and click next. The next dialog determines how you want the layout to appear. Keep the default 'Stepped' on the left options group but change the right one from Portrait to Landscape. You are then asked which Style you would like - I just accept the default 'Office' style.

The next screen asks you for a name for your report, which will also appear as the report's title, so type one in and then select 'Preview' and click 'Finish'. This action causes the report to run, and as it's information is displayed via a paramaterised query you will be asked to provide the 'From' and 'To' dates. Following a brief pause whilst Access generates the report you will see something like this:

Now we're getting somewhere! There's work to be done still, but at least you can now start really to see the fruits of all the labour so far!

Arranging the display of the report is best done in 'Layout' view. Happily this view is quite easy to use, and unlike with the form layout view the boxes don't all contract and expand to the same degree as you adjust one.

I'm not going to go into detail about how to adjust your report. Play around with it, switching between 'Layout' and 'Report' view to assess your changes. Have a look at the long list of properties you can change on the property sheet - most are fairly intuitive - 'Text Align' for example allows you to choose to have the text left aligned, centred or right aligned among others. I learnt how to do this largely by trial and error - trust me  - it's the best way!

You can then repeat the above exercise on the 'qrySportsByStrat' query - and that will conclude the detailed 'how to' for this stage of the trading database.  I trust you have found it useful.

Lazy man's querying

As we now have a query that provides us with our trading results sorted by sport, it would be a good idea to have one that does the same but sorting them by Strategy. The good news is that it is very easy to do, and extremely quick!

Right click on qryTradesbySport and copy it. Name the paste 'qryTradesByStrat'. Open the designer and change just one field. I'll leave it to you to work out which, but the finished result below might provide a clue:

We'll have a look at how to produce the report layout for both queries next time, and once we've done that we'll move on to the challenge of working out how to cope with a bank, credits, winnings, withdrawals et al.