Help with setting up database!!

[Jame]

Okay, I set-up databases all the time with relationships, but this one has me
boggled. I want to create a database for a horse show. I know that I need
to have at least three tables- People/Horse combo, Classes, and Entries. The
difficult part is that the show is really a series of three shows. Each of
the shows contains the same classes, but in a different order. We can use a
primary key of the state issued class number. My problem is that I want to
receive their entries , enter them once, and they will automatically be
entered for all three. I need to also track placings, times, and scores for
each show, so I don't want to create a table of classes with "Show 1 Time,
Show 2 Time," etc. I have other databases that run these shows fine, but
they are one time, one day things. I thought I would work on this now,
instead of getting down to crunch time, but it is really creeping up on me!
I really don't want to use a macro or a Query to complete these multiple
entries, as someone can walk up at any show and choose to enter, and it would
need to carry through to the remainder of the shows. Any help would be
greatly appreciated!

 

[Fred]

I noticed that nobody answered. If you forgive my directness in an attempt
to be helpful.

Your question involves / requires the underlying table design. For that you
need to define the key data elements / entities of your real-world situaiton
that you need to database, and other than a few spotty comments yo haven't
done that.

The solution to your (enter in all three shows" queston will also be built
on that foundation. But to explore 2 other questions on this item, if it's
really universally true that a person is registered for all three shows,
then there could be just one table of people who are registered (i.e. for all
three shows.) Of course, this would not be true if there are exceptions
that ust be tracked. An, of course, registration and participation may be
two different things, hence, back to my first paragraph.

Finally, why rule out one of the main tools of Access (queries)? I suspect
that what you really meant is that you didn't want to have to manually launch
something to load registration (not even hit a button?) into the other
three shows.

Hope that helps a little.

 

[Jame]

Okay. I have three tables-

Participants
Number (Primary Key)
First Name
Last Name
Horse Name
Child Age
Horse Age
Address
City
State
Zip
Phone
Horse Height

Registration
RID (Primary key)
Entry (Participant Table Number Field)
Status (Checked In, Scratched, No Show, Excused, Off Course)
Class (State Class Number from Classes Table)
Placing
Time
Points

Classes
State Class Number (Primary Key)
Show 1 Order
Show 2 Order
Show 3 Order
Name

I am trying to avoid having Show1 Time, Show2 Time, Show3Time, Show1 Place,
Show2 Place, etc. in the Registration table. Technically, all participants
are required to register before hand via mail so that everything can be ready
for the first show, although sometimes people do enter more classes at the
shows. Once someone is entered for the first show, they should be entered
automatically into Shows 2 & 3 for the same classes. I don't know if I need
another table or not for that. Also, taking time to run a query in the amist
of a crazy horse show where people are scratching and everyone is checking in
at the last minute is not very easy to do.

Thanks for any help you can provide.

 

[Fred]

Hello Jame,

This is just a 1/4 of an answer because I rant out of time and have a couple
more questions.

First we need a new noun, because you are using the same one "show" to refer
both individual shows (let's call that a "show" and whatever you call the
trio of shows....let's call that a ShowTrio. This is an entity that will
probably need it's own table, even if a tiny one.

Now, there are a couple of open questions:

What does "registration" actually mean?

1. That they are just eligable to participate in a particular show? In
this case, they register for the SHowTrio not the individual shows.

2. That they are both eligable and intend to participate in a particular
show? In this case, the registration is for the individual shows.


Could you explain the latter fields in your "Class" table?

 

[Jame]

Registrations mean that the person/horse combo (as identified by their
number) enters a class. I guess I need to explain a little further- These
are three qualifying shows for a regional show. The ShowTrio has a master
list of classes. These classes are identified by their state assigned number
(Primary Key of the Classes table). The order of these classes is different
for each show. At the last show, we will need to pick the two best placings
of each person/rider combo throughout the three shows to determine who
qualifies for the next level. That is what the last few fields in the
classes table was for- the running order of each class for each of the shows.
What I typically do at the shows is have a form open that has the class
information at the top and the "registrations" at the bottom where I can
place them as the judge calls them out. The announcer also uses this to pin
the class. I then go to the next class via the form. This eliminates paper
copies of the show order.

Now, typically, the registrations are sent in before hand. They will
compete in the same classes at all 3 shows. The number of people who qualify
for the regional show are determined on the number of unique entries in each
class in ShowTrio. Example- rider 2 shows at shows 1 & 2 and her horse is
injured. She would count towards the unique entry once, as would someone who
only showed in the last show. Here comes the tricky part- if we are close to
meeting the next level of sending an additional person on to regionals, they
may register on-the-fly at the last show as the class is entering the ring.
The last show is crazy because we are figuring out the qualifiers for the
regional show, and someone is also registering them for the next show during
the show- no time to swap screens as we are running a new horse in the ring
every 15 seconds, recording the time, making sure they are on-course, and
announcing. Like I said, any help would be greatly appreciated.

 

[Fred]

Hello Jame,

Sometimes design questions these are a bit of a dilemma because the
respondent needs to fully understand the details (including all the
"what-if's?" of the situation that needs to be databased, and often (as in
your case) there are lots and lots of those details. Rather than ask 20
more questions, I'll just presume/guess some things and go from there.

First I think we again need another noun because this time "class" means two
different things:

- Classification as defined by the state
- An instance of running a class competition in a show. Let's call this
"ClassComp"


The biggest group of unasked questions is whether you need to record the
results of each person (vs. just the top placings in each ClassComp). I'll
assume the latter; if it's the former, you'll just need to move the place to
record results data down one level in the below heirarchy.

I think that you have a 4 level heirarchy of data entities (going top down)

ShowTrio
Show
ClassComp
Instance of registration of a PersonHorse in a ClassComp call it
ClassCompRegis

100% normaliztion would dictate another PersonHorse table, but I'm thinking
not.

The REAL registration is in a ClassComp, not a show or a ShowTrio, although

The upper levels are ancilary tables, they could almost be relegated to
"drop down" lists to pupulate fields in lower level tables but let's not do
that.

What you really want to to automatically create registrations as follows:

Registering in a Show#1 ClassComp creates one for Show#2 & #2 Class Comps

Registering in Show#2 ClassComp creates one for Show#3

Registering for Show#3 doesn't create any additional ones.

To save seconds during your described rushed moments, you probably want to
create three different forms for entry during the above three situations.
Code the first two forms so that whatever (Access/Form) event you choose
creates the proper additional records in the ClassCompRegis table per the
above list.