Team Db - Multiple Scores Per Athlete Over Multiple Meets...

[rattler418]

Ladies and Gentlemen of the UtterAccess[edit], ahem, ACCESS FORUMS...[edit] Community,

I have been researching a bit of a roadblock I am having for some time now, and I must admit that I am at a complete loss on this. Please help me.

I am building a DB for a local gymnastics team. I am capable of getting everything worked out except for this one concept that is integral to the entire purpose. I have the design laid out, the flow worked out, and all of the tables and queries (thus far) set, I just need to figure out how to build a certain relationship, based on the following information.

One gymnast accumulates 4 scores in one meet. She competes in multiple meets, over which her scores from each will need to be averaged, therefore stored (I can do the calculations once I get the data playing nicely).

Each meet involves multiple gymnasts, each with her own set of the four scores, across which a team average must be calculated.

The scores are for the following events: Vault, Beam, Uneven Bars, Floor Exercises

I have attached a screenshot of my current relationship view. Please advise on any required additional tables and how they would relate to each other and to my existing tables.

Thank you very much for your attention in this.

-rattler418

[Rod]

Thank you for your most eloquent post and greeting. Unfortunately we do not comprise the UtterAccess Community but are instead the humble AccessForums.Net rabble. We are willing to forward your request on to our colleagues at UtterAccess but you may have already done this.

Sorry, couldn't resist it.

You need to add a table for event: four records for vault, beam, uneven bars (been in a lot of those after a few beers) and floor execises. Then you need a join/cross reference table between this new event table and your existing tblMeetJoin.

PS. What are first and last names doing on your tblMeetJoin? Shouldn't be there; retrieve them from tblGymnasts2. Same for meet location state and city.

[rattler418]

Rod,

First, please accept my apologies on the incorrect address. I did post this question on Utter Access first, then pasted it in here. I did so with the intent on getting as many opinions on my dilemma as I could, and simply neglected to readdress the audience appropriately. I hope I did not offend.

Secondly, I would like to thank you for your reply. I will try it over the next couple of days and I will let you know my results. I sincerely appreciate your time and expertise.

Regards,

rattler418

[Rod]

Nah! No offence taken. Forgive me for being facetious; it was meant in fun. Get back to me/us anytime. You could normalise your db design further.

[rattler418]

If I am to use a Join table between tblMeetJoin and tblEvents, which field in tblMeetJoin should I declare as the Primary Key? I wasn't sure that join tables even used PKs.

Steven

[Rod]

First, to clear a possible misconception, none of your tables are true join/cross reference tables solving a many-to-many relationship. As they attract additional data apart from the foreign keys they are in fact normal data tables. It's a pernickety point but referring to them as joins may 'colour' your future thinking.

Please note I have introduced a new primary key on tblMeetJoin. You could of course use a compound key of CCGMeetID + GymnastID but I, and many others, find compound keys awkward, especially later into the development. Also I have forgotten to specify ScoreID as the primary key; this is a mistake.

If it's not too late to re-engineer your project, I recommend that you remove the duplicated data from your tables.

Don't hestitate to repost if you have furher concerns.

[Rod]

Just in case you can change nothing, your db relationships will look like this:

[ssanfu]

PMFJI,

If you still can make changes, I would also reccommend:

** Rename "ID" to "GymnastID". "ID" is nor very descriptive,especially if there could be another table with a PK of "ID".

** You shouldn't use special characters or spaces in object names ("#" is the date delimiter). Only use letters numbers and the underscore.