THAT WAS A MESS!!
You'll have to bear with me.
Hopefully this will look better.
RESULTS
RaceNo 363068
Course DONC
Date 27-Mar-10
RTime 14:00:00
Go SFT
Age 4
Furs 8.0
Class 2
Prize £31,155.00
Ran 22
Pos 1
Drw 19
Btn 0.20
TBtn 0.20
Horse Irish Heartbeat (IRE)
Wgt 134
OR 89
GRD 91
WA 4
AA 0
PPL 2.22
RAW 100
RAWadj 98
BHB 91
BHBadj 85
HEAL 89
ORADJ 83
HORSE
HORSEID 1
HORSE A B Celebration
RACES
RaceNo 363068
Course DONC
Date 27-Mar-10
RTime 14:00:00
Go SFT
Age 4
Furs 8.0
Class 2
Prize £31,155.00
OR 91
GRD 4
WA 0
DECS
RaceNo 448252
Course PONT
Date 24-Jul-11
RTime 16:25:00
Go GF
Furs 6.0
Class 3
Horses 1 Barney McGrew (IRE)
2 Bathwick Bear (IRE)
3 Cocktail Charlie
4 Confessional
I have put the tables with title first.
fields on the left with one set of data next to it.
The RESULTS file holds all the seasons results(only 1 horse shown)
The HORSE just the id and name of the horses( ID must be PK I assume)
The RACES file is the race detail
The DECS are the details of the days races and runners.
looking at it I'm not sure I need RACES as the race details are in the DECS table and eventually the RESULTS table.
What I eventually want is to extract the last 6 results from the runners in the DECS table and display in rating order.
I'm worried that I may get the primary keys and relationships wrong before I start. Or perhaps I need to break things down further.
Davey H
--- In MS_Access_Professionals@yahoogroups.com, Crystal <strive4peace2008@...> wrote:
>
> Hi Davey,
>
> did this come from an XML dump? It appears to be structured. I see these tables ... and more ...
>
> Events
> - EventID, autonumber -- PK
> - EventName, text (ie: Saturday Races)
> - EventDate, date/time
>
> Courses
> - CourseID, autonumber, PK
> - CourseName
>
>
> Races
> - RaceID, autonumber, PK
> - RaceNo
> - CourseID
> - PrizeAmt
> - Winner
>
>
> Horses
> - HorseID, autonumber, PK
> - HorseName
> - hOwner
> - hDOB, date/time, date of birth
> - hAge, number, in case DOB is not available
> - hWeight, number
>
> - etc
>
> RaceHorses
> - RaceHorsID, autonumber, PK
> - RaceID, long, FK to Races
> - HorseID, long, FK to Horses
> - RunTime, date/time
>
> if prizes are trophies or something that revolves, you will need to put PrizeID in the Races table
>
> Prizes
> - PrizeID, autonumber, PK
> - PrizeName, text
> - PrizeAmt, currency
>
> PK = Primary Key
> FK = Foreign Key
>
> for structure ideas, watch the videos in the Learn Access playlist here:
> http://www.YouTube.com/LearnAccessByCrystal
>
> and read this book (especially Normalization and Relationships chapters)
>
>
> Access Basics by Crystal (Bill Mosca's site)
> http://thatlldoit.com
> Free 100-page book that covers essentials in Access
>
> and get a couple good big reference books for Access such as a book in John Viescas' signature ;)
>
> Warm Regards,
> Crystal
>
> *
> (: have an awesome day :)
> *
>
>
>
>
>
> ________________________________
> From: John Viescas
>
> Davey-
>
> Well, the data you pasted is almost impossible to read! If you have a table for
> horses, then put the linking HorseID into the Results table to link the result
> to the horse. In an Access form, you would use a combo box on HorseID in the
> results form to pull in the horse names to make it easy to pick the right ID.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: Davey H
> Sent: Tuesday, October 25, 2011 s
>
> Even though I'm the wrong side of Cemetary Hill I joined the computer
> revolution and taught myself Excel.
> My hobby is Horse Racing and I had been keeping the results in an Excel
> File. However that has grown to a huge spreadsheet and I found it very
> hard to learn how to extract the information needed.
> I therefore tried to learn Access but am finding it very difficult to
> understand in places in particular keys and relationships. Below are the
> tables. the HORSE table has ID as primary key but after that I'm at a
> loss trying to decide primary and foreign key connections between
> tables.
> I would appreciate your advice.
>
> Many Thanks
> Davey H
>
> RESULTS DECS HORSE
> RaceNo 466066 466080 RaceNo 1
> HORSEID HORSE Course DONC NEWB Course
> ASCO 1 A B Celebration Date 21-Oct-11
> 21-Oct-11 Date 1/1/2011 2 A Boy
> Named Suzi Rtime 15:45 16:05 Rtime 14:00
> 3 A P Ling Go GD GF Go GD
> 4 A Pocketful Of Rye (IRE) Age 4 4 Furs
> 6 5 A Southside Boy (GER) Furs 6 10
> Class 4 Class 2 4
> Prize 1111 Prize £11,450
> £4,528 Horse A P Ling
> Ran 22 15 Pos
> 1 1 Drw 20
> 14 RACES Btn 2.25
> 0.1 RaceNo 466066 466080 466067 466081
> 466068 TBtn 2.25 0.1 Course DONC
> NEWB DONC NEWB DONC Horse Bohemian Melody
> Jiwen (CAN) Date 21-Oct-11 21-Oct-11 21-Oct-11
> 21-Oct-11 21-Oct-11 Wgt 122 129
> Rtime 15:45 16:05 16:20 16:40 16:55 OR 91 83
> Go GD GF GD GF GD GRD 105 81
> Age 4 4 4 3 4 WA 4 8 Furs
> 6 10 14.6 8 10.3 AA 0 0
> Class 2 4 4 5 4 PPL 2.95 1.77
> Prize £11,450 £4,528 £4,528 £2,588 £4,528
> RAW 102 85 Ran 22 15 18 12 19
> RAWadj 88 82 BHB 105 81 84 75 85
> BHB 107 81 WA 4 8 0 9 7
> BHBadj 89 70 AA 0 0 0 2 0
> HEAL 98 78
> ORADJ 80 72
>
> [Non-text portions of this message have been removed]
>
Tidak ada komentar:
Posting Komentar