Computer Timeform and Access or SQL help required

This lot should help

Grey Shot 31 Mar 09:35
I have Computer Timeform in the new version which runs on SQL server. I have limited computer skills but need some help.
I need to report engine (not sure if thats the description) which enables me to build a dashboard with all the Field headings withing SQL and then for me to run a reports
for example, I would want all the runners runing today with a list of past Timeform rating + going details.
Timeform will not give any support in this area.
Is there some kind of idiot proof software I can get to deliver what I am trying to do?
Thanks in anticipation
Grey Shot 31 Mar 09:35
Will be popping out this morning so will reply to anyone later, Thanks

Feck N. Eejit 31 Mar 10:42
Grey Shot, I used to import the data from the old computer timeform into my own database. When they started up the 'new' version I did some research. Unless they've changed it since, the following will allow you to get at the tables from within ms access.

Viewing the data within Access
______________________________

I was using windows XP when I did this so, I've no idea if it will work on earlier/later operating systems.

First set up an ODBC connection to the timeform data.

In 'Control Panel' double click 'Administrative tools' and then double click 'Data Sources (ODBC)'.

In the 'User DSN' tab click 'Add'.

From the list of drivers highlight 'SQL Server' and then click 'Finish'.

In the 'Create a New Data Source to SQL Server' window enter "TFData". In the 'Server' box enter the name of your machine (you'll find this in 'System Properties', 'Computer Name' tab listed as 'Full computer name:') and then click 'Next'.

Tick the 'With SQL Server authentification ......." radio box. For both 'Login ID' and 'Password' enter "ctf" then click 'Next'. [At this stage if you recieve an error message about a failed connection then you've done something wrong].

Tick the 'Change the default database to' box and, from the drop down list, select 'CtfData' and then click 'Next'.

Now click 'Finish' and then 'OK' and 'OK' again to close the 'ODBC Data Source Administrator'. Finally, close 'Control Panel'.

Now start MS Access.

From menu choose File | New | Blank Database, browse to timeform folder and name it "TFDataLink".

From menu choose File | Get External data | Link Tables. In the "Files of type" combo select 'ODBC Databases()'. Click the 'Machine Data Source' tab and then select 'TFData'.

Enter the password "ctf" and click "OK". In the 'Link Tables' box click 'Select All' and click 'OK'. Just click 'OK' in response to all the 'Select Unique Record Identifier' boxes that come up.

A link to each of the tables should now appear in your new database.

** Many thanks to baynet for supplying the bulk of the above instructions**



Feck N. Eejit 31 Mar 10:42
I've compiled a list of the main tables below along with a list of their (crazily named) fields and the corresponding field in the old CTF database. Those fields marked with a ? are either new unknown fields or because new fields leave ambiguity.


dbo_TFA0CPP = Performance_Comment

A0AANB Course_Code
A0AADT Race_Date
A0ACNB Race_Number
A0AACD Horse_Code
A0APST C0mment_Type
A0B9NB ?
A0AWTX Comment


dbo_TFA1CPP = Race_Comment

A1AANB Course_Code
A1AADT Race_Date
A1ACNB Race_Number
A1APST Comment_Type
A1B9NB ?
A1AWTX Comment


dbo_TFA6REP = Meeting

A6AANB Course_Code
A6AADT Race_Date
A6AFST Race_Surface
A6GGNB ?
A6CWST Race_Day
A6CXST ?
A6CANB ? Perspective_Number


dbo_TFAAREP = Course

AAAANB Course_Code
AAAAST Course_Name_2_Ch
AAAATX Course_Name
AAABST Course_Name_3_Ch
AAACST Course_Racing_Type
AAADST Timing_Type_Flat
AAAGST Timing_Type_Jump


dbo_TFAIREP = Race

AIAANB Course_Code
AIAADT Race_Date
AIACNB Race_Number
AIAEST Distance_Type
AIAFST Race_Surface
AIBXNB TRW_1
AIBYNB TRW_2
AIBZNB TRW_3
AIB0NB TRW_4
AIB1NB TRW_5
AICANB Perspective_Number
AIAQST Going
AIAWST Type_Of_Race
AIC9NB Distance_Furlongs
AIDANB Distance_Yards
AIA8ST Season_Type
AIBFST Racing_Type
AIE0NB Time_of_Race
AIBOST TV_Status
AIAAVA Value_Added
AIE1NB Age_Limit_1
AIBPST Age_Limit_2
AIBQST Sex_Limit
AIE2NB Rating_Limit
AIABVA Winning_Prize_Money
AIAKRT Race_Time
AIE3NB Number_of_Runners
AIB2ST ?
AIADWG Minimum_Weight
AIFONB BHB_Mark
AIHWNB ?
AIHXNB ?
AIHYNB ?
AIHZNB ?
AIH0NB Number_of_Fences
AIFNTX Race_Name


dbo_TFAKCPP = Horse

AKAACD Horse_Code
AKABCD Trainer_Code
AKACCD Owner_Code
AKAICD Dam_Code
AKAJCD Dam_Sire_Code
AKAHCD Sire_Code
AKAPTX Horse_Name
AKB7NB Foaling_Year
AKAXTX Foaling_Date
AKATST Horse_Colour
AKAUST Horse_Gender
AKBBST Horse_Colour_2
AKBCST ?
AKBEST ?
AKCLTX Horse_Sort_Name


dbo_TFAPREP = Trainer

APABCD Trainer_Code
APB8TX Trainer_Name
APCHTX Training_Establishment
APA6ST Trainer_Type
APA7ST ?
APBGST Trainer_Status
APSORT Trainer_Sort_Name


dbo_TFAQREP = Owner

AQACCD Owner_Code
AQCCTX Owner_Name
AQBHST Owner_Colours
AQSORT Owner_Sort_Name


dbo_TFARREP = Jockey

ARADCD Jockey_Code
ARABCD Trainer_Code
ARCGTX Jockey_Name
ARACWG Riding_Weight
ARA4ST Jockey_Type
ARA5ST Jockey_status
AREJNB Riding_Claim
ARBIST ?
ARSORT Jockey_Sort_Name


dbo_TFAWREP = Pedigree

AWAECD Pedigree_Code
AWCKTX Pedigree_Name
AWENNB Pedigree_Foaling_Year
AWEONB Pedigree_Rating
AWA9ST Pedigree_Symbol
AWBAST Pedigree_Gender
AWCVTX Pedigree_Sort_Name
AWB9ST ?


dbo_TFAZCPP = Performance

AZAANB Course_Code
AZAADT Race_Date
AZACNB Race_Number
AZAACD Horse_Code
AZABCD Trainer_Code
AZACCD Owner_Code
AZADCD Jockey_Code
AZCJST Distance_Beaten_Code
AZDJST Horse_Equipment_Code
AZAHNB Weight_Carried
AZB6NB Draw
AZCBNB Official_Position
AZCFNB Timefigure
AZBJST Performance_Status
AZEPNB Distance_Beaten
AZEQNB ?
AZBKST ?
AZERNB Performance_Rating
AZBLST Performance_Symbol
AZESNB BHB_Rating
AZETNB Long_Handicap
AZEUNB Overweight
AZEVNB Apprentice_Claim
AZEXNB Price_Numerator
AZEYNB Price_Denominator
AZBMST Price_Status
AZE4NB Difference_at_Weights
AZE5NB Rating_Adjustment
AZFSNB Position_Past_Post
AZACVA Prize_Money


dbo_TFBACPP = Meeting_Comment

BAAANB Course_Code
BAAADT Race_Date
BAA8ST Season_Type
BAAPST Comment_Type
BAB9NB ?
BAAWTX Comment


dbo_TFBTCPP = Past_Ratings

BTAANB Course_Code
BTAADT Race_Date
BTACNB Race_Number
BTAACG Horse_Code
BTI4NB Rating
BTE1ST Symbol
BTI5NB Adjusted_Rating
BTJNNB Penalty


dbo_TFALCPP = Horse_Comment

ALAACD Horse_Code
ALA8ST Season_Type
ALAPST Comment_Type
ALB9NB ?
ALAWTX Comment


dbo_TFAJCPP = Entries

AJAANB Course_Code
AJAADT Race_Date
AJACNB Race_Number
AJB5NB Race_Entry_Number
AJAACD Horse_Code
AJADCD Jockey_Code
AJDJST Horse_Equipment_Code
AJAHNB Weight_Carried
AJB6NB Draw
AJEBNB Penalty
AJESNB BHB_Rating
AJETNB Long_Handicap
AJEVNB Apprentice_Claim
AJEZNB Saddle_Cloth_Number
AJCWTX Owners_Colours
AJCLST ?


dbo_TFAEREP = Weight_For_Age

AEA2ST WDA_Code
AEAMNB WFA_Distance
AEAVNB WFA_Age
AEANNB WFA_Date
AEAONB WFA_Pounds
AEAPNB WFA_Time


dbo_TFAYREP = Horse_Rating

AYAACD Horse_Code
AYCEST Rating_Type
AYAFST Race_Surface
AYBFST Racing_Type
AYB8NB Rating
AYAOST Symbol

brendanuk1 31 Mar 11:35
Sorry for ignornace, but how do you use the database to get the information you want?
Does Computer timeform come with prebuilt reporting and searching capabilities and if you want a custom one you have to go direct to the sql tables?
Feck N. Eejit 31 Mar 11:59
brendan, It comes with a very basic computer programme that allows you to browse the form. If that doesn't suit you have to query the data yourself using sql or access.
brendanuk1 31 Mar 12:09
cheers feck,
Grey Shot 31 Mar 12:42
Brendan, It also has a very shaky analiser which only refers to past form

Stowe many thanks, I did get that one but I am difficulty ( Through basic ignorance) getting my head round the "Joins" and relationships. I have a table of horses with CTF Horse codes which I need to analyse.


Thanks anyway,

Horse code appears in the following tables. I would think that you should be looking at linking them all via the horse table in your relationships for starters

dbo_TFA0CPP = Performance_Comment
A0AACD Horse_Code

dbo_TFAKCPP = Horse
AKAACD Horse_Code

dbo_TFAZCPP = Performance
AZAACD Horse_Code

dbo_TFBTCPP = Past_Ratings
AZAACD Horse_Code

dbo_TFALCPP = Horse_Comment
AZAACD Horse_Code

dbo_TFAJCPP = Entries
AJAACD Horse_Code

dbo_TFAYREP = Horse_Rating
AYAACD Horse_Code

Next I'd link the Race_Dates vi the race table

dbo_TFA0CPP = Performance_Comment
A0AADT Race_Date

dbo_TFA1CPP = Race_Comment
A1AADT Race_Date

dbo_TFA6REP = Meeting
A6AADT Race_Date

dbo_TFAIREP = Race
AIAADT Race_Date

dbo_TFAZCPP = Performance
AZAADT Race_Date

dbo_TFBACPP = Meeting_Comment
BAAADT Race_Date

dbo_TFBTCPP = Past_Ratings
BTAADT Race_Date

dbo_TFAJCPP = Entries
AJAADT Race_Date

Next the Race_Number again all links to Race_Number in the Race table

dbo_TFA0CPP = Performance_Comment
A0ACNB Race_Number

dbo_TFA1CPP = Race_Comment
A1ACNB Race_Number

dbo_TFAIREP = Race
AIACNB Race_Number

Cheers Stowe, much appreciated. Looking at your user name I was wondering if you were linked to Walthemstow dogs?

If So I can tell a tale or two :o)


Many thanks

Yes, it's my 2nd home. If you haven't come accross my website, you may find some intersting stuff there http://www.sma**on.com/

Next Course_Code link all to the Course_Code in the Course table

dbo_TFA0CPP = Performance_Comment
A0AANB Course_Code

dbo_TFA1CPP = Race_Comment
A1AANB Course_Code

dbo_TFA6REP = Meeting
A6AANB Course_Code

dbo_TFAAREP = Course
AAAANB Course_Code

dbo_TFAIREP = Race
AIAANB Course_Code

dbo_TFAZCPP = Performance
AZAANB Course_Code

and this one

dbo_TFAJCPP = Entries
AJAANB Course_Code

and also this one

dbo_TFBTCPP = Past_Ratings
BTAANB Course_Code

Trainer_Code via Trainer table

dbo_TFAKCPP = Horse
AKABCD Trainer_Code

dbo_TFAPREP = Trainer
APABCD Trainer_Code

dbo_TFARREP = Jockey
ARABCD Trainer_Code

dbo_TFAZCPP = Performance
AZABCD Trainer_Code

Jockey_Code via Jockey table

dbo_TFARREP = Jockey
ARADCD Jockey_Code

dbo_TFAZCPP = Performance
AZADCD Jockey_Code

dbo_TFAJCPP = Entries
AJADCD Jockey_Code

Thanks Stowe, I spent my early youth in Dog racing. I will look at the web site. I havent been to the dogs for years but from the age of 12 till about 24 I worked full time. Worked at Beaverwood and then Galley Hill (wenbley and Walthamstowe) I used to be a good friend of John Coleman.

I will have a play with the tables at some point but Im engrossed in the racing at the moment.

Thanks for all your help.

I had my first greyhound with John Coleman and know him fairly well. I still live up the road from his kennels.
Remember any of my old greyhounds?

Sandy Trail ca 1989
Bilocat ca 1989
Westmead Mario Nov 93
Shotgun Harry Sep 94
Sineads Phantom July 95
Fool For You May 97
Citation Aug 99
Shotgun Magic June 01
Shotgun Kate Nov 02

I remeber Sandy Trail the rest may be after my time , Happy days Eh !

Just got the kids round for a BArbi , will look in later

Thanks for all your help

ttt for grey