Subform data determined by 2 fields on mainform

S

sbooth

Registered User.
Local time
Today, 14:52
Joined
Mar 16, 2008
Messages
35
I am doing a database for a small horseshow circuit. I originally designed the database with an append query that filled a table to join tblShow and tblClasses. I am trying to change the database to avoid doing this. I am concerned that the users will not be able to do this correctly.

I have the following:

tblEntry
EntryNum_pk
HorseNum_fk
RiderNum_fk
TrainerNum_fk
OwnerNum_fk
AssignedNum
ShowNum_fk

tblShowClassEntry
EntryNum_fk
ClassNum_fk
EntryFee
PlacingNum_fk

tblShow
ShowNum_pk
Show

tblClasses
ClassNum_pk
ClassNumber
Class

I have a frmEntryTabbed that allows the user to enter the information in tblEntry. I have a frmEntry_Subform that allows the user to enter the information in tblShowClassEntry except the placement. What I am trying to figure out is how to create a form that allows the user to enter the placement. The user needs to be able select the show and class and in the subform only see the EntryNum that fit that criteria. I have frmResults, but I am not sure how to limit the data in the subform. I am attaching a copy of the database if anyone would like to look at it.

I would appreciate any suggestions. Keep in mind that it is a db in transition.
 

Attachments

I love this kind of problem. However, I'm having trouble interpreting what you're asking for help for. I typically don't name objects with the same naming convention you're using and your post doesn't make sense to me.

One note:
Your design seems to be inproperly normalized, though, as pointed out earlier, it is difficult for me to tell for sure.

Since you were willing and able to post your database, would you be so kind as to define which objects (table and form names) you are talking about? And define what you mean by results (I don't see that in your table design)?
 
Thank you for your reply. I realize I must have expected a little mind reading. Sorry about that.

Let me see if I can explain.

We are having a group of horseshows in 2008, probably 8 (tblShow). Each horseshow will have the same classes (tblClasses). The same horse may or may not show at more than 1 horseshow. So, the participant will fill out an entry blank with information like horsename, rider, owner, trainer, etc (tblEntry). I would also like to store which horseshow they are participating in here. They will also circle the classes they want to participate in (tblShowClassEntry). However tblShowClassEntry also contains PlacingNum_fk. This is how the entry placed in this particular class (result). tblPlacement contains possible placings (1st, 2nd, 3rd, etc). Obviously, when the entry is first put in the computer no one knows how the horse will place in any of the classes. Therefore, it is defaulted to none. frmEntryTabbed and frmEntry_Subform are how this information is filled in.

Now, as the show is progressing a judge will turn in a card. Let's say classes 1 thru 3. The user will need to use a form to pull up class 1 for the correct horseshow in the mainform. The subform will then show the entries for that class and allow the user to select the placings. This is done in frmResults and frmResultsSubform. This is where I have problems because there is not a direct relationship between tblShow and tblClasses.

Now to explain some of the other tables. There is a payback for each class. The amount of an entries winnings is dependent on the number of horses in the class and how the entry placed in the class. tblPaybackChart stores the information that is used to calculate this. Usually a participant will elect to have their winnings deducted from their entry fee. So, if they enter 5 classes at $9 each, their entry would be $45. However, if they win $12, they can pay $33. Sometimes, the participant would rather pay $45 and get $12 back in cash. tblPaybackPayment would store this information. tblEntryPayment stores the amount the participant has paid for their entry fee. tblPaymentType stores how payment was made (check, cash, etc)

Also, we are doing awards for the circuit based on a point system. It is setup similarly to the payback, in that the number of points awarded is based on the number of horses entered in the class and how the horse places. tblPointsChart stores the information that is used to calculate points.

tblDivision is a way of grouping like classes together.

I hope I have given enough information. If anyone has any questions please feel free to ask. I appreciate the help!
 
sbooth said:
Now, as the show is progressing a judge will turn in a card. Let's say classes 1 thru 3. The user will need to use a form to pull up class 1 for the correct horseshow in the mainform. The subform will then show the entries for that class and allow the user to select the placings. This is done in frmResults and frmResultsSubform. This is where I have problems because there is not a direct relationship between tblShow and tblClasses.
Click to expand...

This is the only place you mention a problem. Is there a reason you put the Show FK in the Entry table? I would think that the Show FK should be in the "ShowClassEntry" table, which would essentially solve your problem. This would allow the same entry (i.e. no duplicate typing) to participate in multiple shows and classes over time. You really may want to re-think this part of your design. What if you have multiple riders at a given show for the same horse?

Also, it won't hurt anything to have the ShowFK in both of these tables, though you really want to understand your reasoning for this before you do it.

I'm not sure who wrote the book that thought it was a good standard to put "tbl" in front of table names...it really confuses the design. The standard in the database world seems to be to name tables a very descriptive name in the plural form and column names in singular form. It makes talking/writing about it in English much easier, almost as though you were talking about the real-world objects.
 
Thank you so much for trying to understand what I am doing.

The problem with using the same entry with multiple horseshows is that the information may change from show to show. For instance a horse may be sold, or the horse's rider may change from one horseshow to the next, or the horse may be trained by a different person at different horseshows. When it is time to calculate year end points, the points stay with the horse regardless of rider, owner, or trainer, but I do not want to change history.

Example:

Horseshow 1 Trigger is ridden by Sarah, owned by Mary, Trained by John
Horseshow 2 Trigger is ridden by Denise, owned by Mary, Trained by John

If I use the same entry for multiple horseshows then it would appear that Denise rode Trigger at both shows. However, if I create a different entry for the 2 horseshows I can see that Sarah rode Trigger in show 1, and Denise rode Trigger in show 2.

As far as multiple riders for one horse at a given horseshow, currently we require 2 entries be filled out for this. It actually makes it easier because each entry is assigned a number that the rider wears. This way they don't have to pin and unpin their number.

As far as using show_fk in both tblEntry and tblShowClassEntry, I hadn't thought of this. So, in frmEntryTabbed I would select the show_fk, use a query to fill that information in on tblShowClassEntry for each class entered. What do you think?
 
I thought about this at lunch. I think putting the FK to "Show" in ShowClassEntry is the way to go and it makes a lot more sense from a data modeling view point. Then you will be able to build a form more easily, including your last scenario, if you so choose.
 
George,
Thanks again for your help. I was thinking about this. Do you think that it would be better to only put show_fk in tblShowClassEntry or put it in both tblShowClassEntry and tblEntry?

Like I said earlier, I don't think it would be smart to have more than one horseshow's classes tied to the same entry. However, the more I thought about it I realized that I could probably put in safeguards to ensure this does not happen.
 
Think about what you're doing and why you're capturing the data.

One of these is data about the entry. Is the entry complete without which show it is about? I'm guessing that the answer is no, thus, you need the ShowID in the entry table.

The other is data that ties the entry to the actual event, if I recall correctly. Since the show is a definition of the event, ShowID should also be in the ShowClassEntry table.
 
Thank you for your help. I just want to make sure that I make this as logical and stable as possible.

Before I continue just one more question if you don't mind. Do you think that the relationships between tblPeople and tblEntry is okay? I have gotten some criticism for this because it appears that there are 3 tables storing the same data. What I am trying to do is have 3 relationships between tblEntry and tblPeople.
 
Sorry it has taken so long to reply. Kinda busy with requests at work.

Don't worry about what other people say. If you're sure there are only going to be 3 relationships between People & Entry, keep it the way it is. If you think that the number of people involved in an Entry might change, create a junction table with a lookup table.

As I recall, you're not storing the exact same data in these tables, you're storing data about 3 different kinds of events in 3 tables. Big difference. You could put all 3 types of events into 1 table but that makes no sense. Stick with your design, unless it doesn't suit your needs.
 
Thank you George. I am finally able to work on my project again, and I was hoping that you would be able to help me with something.

I have changed the tblEntry and tblShowClassEntry to include Show. I have also changed frmResults and frmResults_Subform to reflect these changes. If I open frmResults it is now based off tblClasses, and I can use the navigation buttons to go from class to class. The subform shows the entries in the classes and the results if there are any. However, it shows the entries for all shows. I have created frmShowFilter that uses a combo box to select the appropriate show and a button to open the frmResults. I don't know how to limit frmResults_Subform to the show selected in frmShowFilter. Would you be able to help me with this?

I have attached a copy of my db in case I was not clear. Thank you.
 

Attachments

I think I would have put the "Show" in the main form and filtered on "Class" in the subform. Doesn't that make more sense? Or, see the next paragraph for a better way.

Think of this as a 1:M:M relationship. 1 show to many classes to many entries. If you were to exactly duplicate that in forms, you would have a main form (Show) with a subform (Class) with a sub-subform (ShowClassEntry). You already have the tables to support this and wouldn't really have to write any code to pass data around. I notice there is no data in tblShowClass...that's wrong. To ensure this data is entered and the design is cleaned up, give tblShowClassEntry a fk from tblShowClass and remove the links from tblShow and tblClass.

It can work the way it is, but that is the better solution.

Also, why do you have a relationship between tblEntry.ShowNum_fk and tblShowClassEntry.ShowNum_fk? This should be removed.

I tried it out the way I suggested and it works fine (with the exception of the missing data).

Let me know if you need more help.
 
Thanks for the quick reply.

The reason tblShowClass is empty is because that is the table I am trying to eliminate. It was setup to be filled with an append query, and it worked fine. However, I was concerned about the users doing this. It just wasn't fool proof enough for the long haul. So, I am redoing the tables. I haven't deleted the table because I have some queries that use the table, and I don't want to delete the table until I have redone the queries.
 
You must log in or register to reply here.

Similar threads

G
Fields determined by data
Replies
5
Views
1,410
jdraw
jdraw
Ramnik
Existing Data in field of subform also filtered by combo box on main form
2 3
Replies
42
Views
3,767
Ramnik
Ramnik
S
Use Max() ?
Replies
3
Views
825
sbooth
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom