The Horse Forum

The Horse Forum (
-   Technology (/technology/)
-   -   Help with MS Access '07? (

RubaiyateBandit 09-02-2010 12:06 AM

Help with MS Access '07?
I made a database in Access 2007 that keeps track of all the people and their horses that ride in a local "Under-the-Lights" show series, along with their points/age group/etc, and then runs some queries that show, for instance, everyone over 18's point total in the speed events. (That's how they determine High Points at the end of the season -- Speed vs. Pleasure, and then by age groups.)
Admittedly, I did it halfway for my own amusement, and halfway for practice for a class I'm in.

Anyway, I have it up and running, there's just some tiny things that bother me. Cosmetic details, really, but I'm having fun and was considering presenting this to my club president.

My tables look like this (well, these are the headings and what they point to. :p):

  • Team ID
  • Rider Name
  • Horses Name
  • Age Group
    (Lookup: "12 & Under"
    "13 - 17"
    "18 & Over")
  • Class ID
  • Class Name
  • Age Group
    (Lookup: "Open"
    "12 & Under"
    "13 - 17"
    "18 & Over")
  • Group
    (Lookup: "Pleasure"

  • Show ID
  • Show Name
  • Show Date

Points Awarded
  • ID
  • Show ID
    (Lookup: [Shows].[Show Name])
  • Show Type
    (Lookup: "Pleasure"
  • Class ID
    (Lookup: [Classes].[Class Name])
  • Team ID
    (Lookup: [Teams].[Rider Name] and [Teams].[Horses Name])
  • Points Awarded
  • Placing
    (Lookup: "1st"
    "No Placing")

Question # 1: In the Points Awarded table, under Team ID.... It shows in the table as [Riders Name]. Is there a way to get it to combine [Riders Name] and [Horses Name] and show both? Say, as "[Riders Name] / [Horses Name]".
I considered simply combining the two, manually, in the Teams table itself. But I like being able to say "This person has this many points total"... say, in the instance where I run two horses. I can see how many points I have under Tanner, and how many under Magic, or I can simply query my name -- ignoring the horse -- and see how many points I have on both horses combined.

Question # 2: Is there a way to limit who can be listed under a show? Say, if I'm entering a new record in Points Awarded for myself, in the July Speed show, in the class Barrels 18 & Over. As I'm typing that in, is Access able to narrow down the look up. If I type in "July Speed Show" have it only show Classes (in the [Class ID] column) that are labeled as 'Speed' and not show the ones labeled 'Pleasure'. Then, as I enter "Barrels 18 & Over" into the [Class ID] have it narrow down the [Team ID] lookup to only those teams who are labeled '18 & Over'.
Did that make any sense at all? :-P

Question # 3: In the Points Awarded table, again. Can I set [Placing] to be automated based upon what I enter in [Points Awarded]? All of the UTL shows have the points set the same (1st place = 6 pts, 2nd = 5 pts, 3rd = 4 pts, etc.), so... if I type "6" into the [Points Awarded] column, it would generate "1st" in the [Placing] column.

I can't think of anything else, but if you can answer any of these questions, or want to point out anything strange I might have in my tables, feel free; I'll be grateful. :D

maura 09-02-2010 06:13 AM

Not sure about # 1, but I know 2 and 3 can be done.

I think the way to solve number 2 is to make separate tables with the class list for each type of show. If you're using a GUI or a dressed up Access form to make your new entries, you'd select the show type in one drop down and then only those classes would show in the next drop down.

3 is definitely possible too, the simplest way might be to put a little lookup table in your table list, and add the "Placing" field from your lookup table into your query. You could also write a formula or some VBA code with a list of If/then statement, but I think the first way is easiet.

Have no idea if that was any help.

All times are GMT -4. The time now is 09:26 PM.

Powered by vBulletin® Version 3.8.8
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
vBulletin Security provided by vBSecurity v2.2.2 (Pro) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging (Pro) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.

For the best viewing experience please update your browser to Google Chrome