Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2007

    Unanswered: New Database Question

    Hey Guys and Gals,

    Sorry but Im a bit overwhelmed with the database thing. Im great with excel spreadsheets and have got this information in a spreadsheet but I though a database would be a much better way of storing and retrieving the information.

    I have just began managing a jockey (rides horses).

    I want a database to record the following...

    Result (finishing position)
    Total Prizemoney Earned (user inputs amount individually)
    Fee (every ride she earns a flat fee, i want this to go in automatically)
    Jockey Share of Prizemoney (to calculate 5% of Total Prizemoney earned)
    Total Earned (fee plus jockey share of prizemoney).

    This is the information that I want to be able to work with and from these monetary amounts, I want to be able to calculate my percentage of the Total earned.

    I have created a few tables - Results (Consisting of Date, Track, Horse , Trainer, Result, Total Prizemoney, Jockey Fee, Jockey Share of Prizemoney, Total Earned), Race Track, Horse Name, Trainer.

    I dont want trainer and horse to be in the same table as horses can be transferred to another trainer, however the past result has to be with the past trainer not changed to the new one.

    I cannot get the jockey share of prozemoney to calculate the percentage or the Total Earned to add up the jockey fee and jockey share of prizemoney. Can this be done? and if so is anyone able to offer any help?

    I also have done forms up for trainer name entry and horse name entry. They are on separate forms, but I was wondering if I can get them on the same form at all? And if so, how can i do this?

    Is anyone able to help in terms of how to set this sort of information up?

    Forgive my rudeness however I have never used access, but the spreadsheet is a bit limited for the retrieval of this info.
    Oh and sorry for all of the questions.

    Thanks in advance I really appreciate your help!


  2. #2
    Join Date
    Feb 2004
    Chicago, IL
    It seems to me you have a solid start. To do any kind of calculations in Access you will most likely include a calculated field in a query. Create a new query and add the Results table, then add the PrizeMoney and JockeyShare, finally you can add caluclated field similar to TotalOwed:[PrizeMoney] * [JockeyShare]. Or if you prefer SQL it would look like [PrizeMoney] * [JockeyShare] AS TotalEarned.

    As far as Trainers and Horses go, it seems like there is a one to many relationship between them. One trainer has many horses, but does one horse have many trainers? If so then you have a many to many relationship. I will assume a one to many relationship. The first thing you need is a Primary Key field in the Trainer table. I would recommend an AutoNumber field (unless you already have an identifying field). Assuming this field is called TrainerID, I would add a similar field to the Horse table (this is known as the Foreign Key). And you would enter the TrainerID of the trainer for that particular horse. Then in a form, link it to the Trainers table, showing any information that you like. Then create a separate form that shows horse information. And finally add the second form to the first using the Subform control and if you named the TrainerID field the same in both tables, you should see a list of Horses for a given Trainer.

    For the Second form (Horse Details) make sure the Default view is Continuous Form.

  3. #3
    Join Date
    Jun 2004
    Arizona, USA
    I would suggest taking a look at the two database tutorials in this thread at a sister site.
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts