Fine tuning the Trades form

A couple of useful little tweaks to further enhance the information available from the main Trades form. We'll introduce three text boxes to show our total trades to date, total P/L and the average P/L on those trades, and we'll also put a text box on the form to display the current date. Again, I think you'll be pleasantly surprised by how easy this is to achieve.

First, we need to create some screen space, so with the Trades form in 'Design View' select the 'Form Footer' and either drag the bottom edge down a bit or go to the properties sheet and select 1cm as the height. Drop four text boxes into the new space, deleting the associate label with the leftmost but keeping the other three labels.

The leftmost box will hold the current date, so select 'Data Source' and bring up the 'Build' dialog as we did before. Type an '=' sign then navigate to Functions / Built-in Functions / Date Time and select 'Now' by double clicking it. Close the build dialog, change to the Format  tab, select the Format menu and choose 'Long Date'. Simple as that. The text box will now display the current date in 'dd/mmm/yyyy' format if you are in the UK.

The second text box will hold the number of completed trades, so put a suitable name in the label, and again bring up the Build dialog. Again type an '=' then go to Functions / Built in Functions / SQL Aggregate and select 'Count' by double clicking it. This will bring up the following: Count(<expr>) - so all we have to do is to substitute <expr> with what we want to count! Navigate to the top item on the left pane of the build dialog, which should read 'Trades' and on clicking it all the fields in our table will appear. It doesn't really matter what we count, but for the avoidance of silly errors I'd be inclined to choose 'TDate' as every trade will have a date!

Repeat this step for the next two boxes, selecting the SQL Aggregate 'Sum' for the Total P/L box, using, unsurprisingly, the 'PL' field as the expression to sum, and select 'Avg' (i.e. Average) for the final box again using 'PL' as the field to average.

Whilst you are about it why not use the conditional formatting to colour the text in these boxes as we did before? You should end up with something a bit like this....

These pieces on Access have repeatedly used the term 'relational database' and we haven't yet really got to grips with what that actually means. In the next exciting installment we'll start looking at relationships between different sets of data and how to implement them. Bet you can't wait :-)