If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with setting up database!!
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! |
#2
|
|||
|
|||
Help with setting up database!!
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. |
#3
|
|||
|
|||
Help with setting up database!!
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" wrote: 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. |
#4
|
|||
|
|||
Help with setting up database!!
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? |
#5
|
|||
|
|||
Help with setting up database!!
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" wrote: 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? |
#6
|
|||
|
|||
Help with setting up database!!
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. |
Thread Tools | |
Display Modes | |
|
|