Hooking it up

We now need to construct the Trades table. We'll start with TradeID, make the data type 'Autonumber' and make sure this field is set as the Primary Key (more about which later). Make the second field 'TDate' and set the data type to 'Time/Date'. Unfortunately we can't just call this 'Date' as that is a 'reserved' word meaning it refers directly to something going on under the hood. The third field is 'SportID' with data type Number, fourth is 'Event' with data type 'Text'. Field 5 is 'StratID' / Number, six is 'Stakes' / Currency, no 7 is 'PL' / Currency and last but not least 'Description' / Memo.

Your table in design view should look like this, notice I have added some text to the 'Description' boxes  - this text will appear in the status bar at the bottom of your Access screen it is useful as an aide memoire for example that a losing trade needs to be entered as '-£xxx.yy' in the Trades table etc.

We have now reached the point where we can start wiring some of these component parts together and get a feel for a working piece of software.

At the bottom of the table design screen is the 'Field Properties' section, and as you can see this defaults to the 'General' tab. Click the 'Lookup' tab and you are confronted with a one line chart with either absolutely nothing displaying, or, if you have got the cursor at the top of the screen on wither the 'SportID' or 'StratID' fields you'll see the word 'TextBox'. Click to the immediate right of the 'x' and a drop down appears - select 'Combo Box', and a whole new world of possibilities opens up to us:

The purpose of this screen is to make the data in the underlying data store presentable to humans via the UI (User Interface). If you remember the intention is not to store the word 'Football' for the sport traded in this table, but the number '1' as a reference to it. You might, as an end user, be able to remember a dozen or so such codes but what a fag that would be! With this screen we are going to tell Access to store number 1, but display 'Football'. Click on the highlighted elipsis.

The query screen show above is shown, select the Sport table from the list and select 'Close'. If you then double click on SportID and Sport (IN THAT ORDER!!!) they will appear on the grid. At that point click the 'x' to close the grid and you'll see the screen pictured above. Click 'Yes'. The resultant screen is shown below:

This is all a lot simpler than you might be thinking, I promise you! Taking the highlighted lines one at a time...

"SELECT Sports.SportID,Sports.Sport FROM Sports" is what is known as an SQL statement and simply tells Access to pull every sportID and every Sport out of the Sports table. The second line, Bound Column 1 tells Access to  store the value of the first column (i.e. SportID) in this field of the Trades table. The third line tells Access to display two columns to the end user, whilst the third line tells Access to hide the first column by setting its width to zero and display the second column to a width of 3 cm. In other words you don't see the number (1 in this case) but you do see the content of the second column (Football) in this case.

To show you how that appears in real life, here's my fledgling Trades table at this stage (with some dummy data obviously!):

This clearly shows the relational model in proper action and in an unhooked up state in the same shot. Here we can see and understand which sports are traded, even if which strategy is being adopted is a mystery unless we can remember what StratID 6 represents.

Effectively you can repeat the above steps to create the look-up for strategies as well, I'll omit the screenshots and jump straight to the query set up screen:

This, you will see, is identical in structure to the Sport one, just with the different table and fields. The end result of the display of the actual underlying data is shown here:

Now we're cooking.