Results 1 to 9 of 9
  1. #1
    arnie is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Sydney Australia
    Posts
    5

    Horses database help required

    Hi to all
    I have the following tables

    TrainerTbl
    TrainerID
    HorseID

    HorsesTbl
    HorseID
    RaceID

    RaceTbl
    RaceID
    RaceDate

    RaceDetailsTbl
    RaceID
    Winner (drop down box from HorsesTbl)
    Second (as above)
    Third (as above)

    I have created relationships between TrainersTbl (HorseID) and HorsesTbl (HorseID) and also
    HorsesTbl (RaceID) and RaceTbl(RaceID)
    and RaceTbl (RaceID) and RaceDetailsTbl (RaceID)

    So when i click on a Trainer it shows the Horses trained.. Good

    When I click on one of the Horses that are trained it shows the race numbers the horse has raced in... Good



    Now when i click on the race number I get nothing I would like the database to show the relevant races

    Please excuse me if i haven't explained it clearly enough

    Hope someone can steer me in the right direction

    Many Thanks

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,605
    I don't see your Trainer table??? Could you post your relationships as a jpg?

    There is a data model here that may help with your data base structure.
    http://www.databaseanswers.org/data_...cing/index.htm

  3. #3
    arnie is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Sydney Australia
    Posts
    5
    Many thanks Orange for your reply
    I have attached the relationship as requested
    I will also check out the link you have sent and get back to you

    Cheers
    Arnie

    Attachment 3125

  4. #4
    arnie is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Sydney Australia
    Posts
    5
    Hi Orange
    Ive spent a couple of days transferring my data to your Horses Database and it works brilliantly

    Thanks
    Arnie

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,605
    Quote Originally Posted by arnie View Post
    Hi Orange
    Ive spent a couple of days transferring my data to your Horses Database and it works brilliantly

    Thanks
    Arnie
    Arnie,
    Glad you got it working!
    It isn't my database, the link (www.databaseanswers.org) is a series of data models (and much more) created by Barry Williams and made available for free. The models are intended as a starting point/guide to ease the pain of starting from scratch when a working logical model has been "tested".

    Your version is your data base and hopefully it was a learning experience.

  6. #6
    arnie is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Sydney Australia
    Posts
    5
    Orange

    Just come across a situation I obviously did not think about

    My HorseTbl and TrainersTbl have a one to many relationship.
    This should of course be a Many to Many relationship as Horses can have many trainers and vice versa

    How do I create/change my one to many to a many to many?

    Thanks
    Arnie

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,605
    Arnie,
    You really don't want a many to many. In a many to many situation, you create a junction table that fits between the original Many to many tables. This removes the "many to many problem".

    For example, Students and Courses. A Student can Take many Courses; and any Course could be taken by many Students. So, you create a Junction Table (let's call it StudentTakesCourse).

    So for Students (studentId, StudentFirstname, StudentLastName ,...) and
    Courses(CourseId , CourseName, CourseLevel,....). and for
    StudentTakesCourse(STCId,StudentId FK,CourseID FK,...) OR
    StudentTakesCourse(StudentId PF,CourseID PF,...)

    In the DataBaseAnswers Horse model
    you'll notice between Horse and Races (which would be M:M) there is a junction table
    Horse_Race_Participation with (Horse_Id PF , Race_Id PF, Jockey_Id,...).

    In the junction tables in Barry's models, he uses a Composite Primary Key. The primary key of the junction table is composed of the Primary Key of each of the tables in the M:M.
    Some people will use a separate primary key for the junction table and it will be something like(using my "OR" example above) StudentTakesCourse_Id that I shortened to STCId. It will be an autonumber. And the StudentId and CourseId will have a unique index.

    You can put a junction table between HorseTbl and TrainerTbl, say
    HorseTrainedByTbl(HorseId, TrainerId,..) This will have a composite primary key
    made up of both HorseId and TrainerId. This will allow for a Horse trained by many Trainers, and/or a trainer who Trains many Horses.

    The "..." in the junction and other tables just means "and any other fields that apply to this table". Read that to mean any other fields you may need that are dependent on the table's Primary Key.

    Hope this helps.

    Here's a link http://megocode3.wordpress.com/2008/...unction-table/

    Last edited by orange; 04-16-2011 at 06:38 PM.

  8. #8
    arnie is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Sydney Australia
    Posts
    5
    Hi Orange
    Wow thats is quite a reply for me to take in

    I'll get back to you once Ive taken a good look at it

    Thanks once more for your help with this

    Regards
    Arnie

  9. #9
    charles is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Apr 2011
    Posts
    1
    Hey Arnie,

    I'm glad to see you were able to find a good model for your data structure; I've got something promising myself, but I haven't been able to find any good data. Do you think you could help point me in the right direction, or perhaps provide some sample to work with? Any information would be greatly appreciated.

    Thanks,
    Charles

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Valadation required
    By bopsgtir in forum Access
    Replies: 45
    Last Post: 01-08-2011, 11:25 AM
  2. Hello! Help required if possible please...
    By MarkGLyons in forum Access
    Replies: 21
    Last Post: 12-28-2010, 05:36 AM
  3. Simple database required
    By Steve in forum Database Design
    Replies: 2
    Last Post: 03-09-2010, 06:34 PM
  4. Access Database Problem: HELP required ASAP
    By maverick in forum Queries
    Replies: 1
    Last Post: 01-06-2010, 04:47 AM
  5. immediate help required
    By pdurgi in forum Access
    Replies: 1
    Last Post: 06-05-2009, 08:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums