Can you give some examples of the type of "tracking" that you have in mind? There are a lot of possibilities that meet that description.
I don't think you'll need your dashboard table. Most likely, you'll be able to work from layouts that reference some combination of Athletes and Assessments.
Some tracking I would like to include is:
1. Top 10 scores for a test, like a leaderboard, that I can filter. An example would be the top 10 40-yard sprint leaders.
2. Individuals progress and Personal Records. I can graph a person's 40 yard sprint times and also show what their all time best is.
I would want to be able to filter these by:
A. Date (all-time, past month, week, etc.)
B. Athlete Groups (gender, sport, age, etc.)
1 A filtered portal to Assessments can do that. The relationship you set up for the portal will determine the layout to use--maybe you will need that dashboard table after all. The top 10 part could be produced by having a 10 row portal where the portal or the relationship is sorted so that the 10 ten are the first 10 related recrods.
2. Should should be able to chart data from assessments on a layout based on Atheletes or from the assessments layout. If you do this from Athletes, you'll need to use some extra fields as part of the relationship to control the set of related assessment records that supplies data to the chart. If you do htis from a layout on Assessments, you can do this same filtering by performing a find to produce a found set of just those records. Such finds can be scripted to make them faster and more user friendly.
I appreciate it Phil. Starting to get a grasp on what I need to do. I want to start getting some data to mess with. In the picture I had a scores table. So in my assessments table, I will create a record that includes the date, athlete (drop down list from the athlete name table).
I will then want to add a portal to add 1- however many tests I want to perform in that assessment. It will include the test category , the test name and unit of measurement (from protocol table). I.e. "Speed" - "40 Yard Dash" - "seconds"
Do I need the separate scores table to add the score field so I can start getting scores matched up with the date, athlete, and test protocol? Or can this be included in the assessment field?
Is it a child to assessment along with protocol?
How do you intend, on paper, to work with "scores"? How does your assessment data produce such a score?
Not sure I exactly get what you are saying, but I'll try to explain the process. I'll set up a scenario where height, weight, and 40 yard sprint time is measured.
An Athlete comes in for a new Assessment. On 1/1/2013 this athlete completes an Assessment that includes the Height Test and Weight Test.
Date: 1/1/2013 Athlete: Joe Blow
Category Test Score units Body Measurement Height 70 inches Body Measurement Weight 175 pounds
This assessment is submitted to record.
On 1/15/2013 Joe comes back in for another assessment, but this time we just want to measure his weight and get his 40 yard time.
Date: 1/15/2013 Athlete: Joe Blow
Category Test Score units Body Measurement Weight 180 pounds Speed/Agility 40-Yard 4.3 seconds
With this data I should be able to go into Joe Blows profile and see all his tests sorted by date or category. For example:
1/1/2013 - Body Measurement - Height - 70 - inches
1/1/2013 - Body Measurement - Weight - 750 - pounds
1/15/2013 - Body Measurement - Weight - 180 - pounds
1/15/2013 - Speed/Agility - 40-Yard - 4.3 - seconds
As I get more data for this athlete and others, it will let me graph the data for Joe and other athletes I put in. Is Joe getting faster? losing weight? How fast is he compared to other boys his age?
Does that help?
Ok, so your "score" is a measurement. Don't see any reason why a single number field in the Assessments table can't record that information. I am assuming that you would add new records to assessments each time an athlete is assessed.
(In my mind "score" suggested that you might be using a measured time/weight/distance to compute a "score"--possibly from multiple assessments.)
So if I add a field into the assessment table called score then how do i link it to different tests to make sure when I have an assessment with multiple tests that the score is associated with the appropriate test?
Category (protocol table) Test (protocol table) Score (assessment table) Unit (protocol table) Body Measurement Height 70
Speed/Agility 40-Yard 4.3 seconds Power Vertical Jump 27 inches Strength Bench Press 1RM 265 pounds
Note that this data entry would be in the assessment table which above the test score input portal, I will have the date with the athlete name.
Isn't each test linked to a different Protocol record? One protocol for each different test?
I just noticed that I missed something. I've been picturing your relationships as:
Athletes::__pkAthleteID = Assessments::_fkAthleteID
Protocols::__pkProtocolID = Assessments::_fkProtocolID
and these are not the relationships that you actually posted here.
And your oringal post is quite clear, I just didn't read it correctly.
My comments about assessments should actually refer to Protocols and your score field should be in Protocols, not assessments.
I think I'm getting confused on the wording. protocols and assessments are getting mixed up. I dont mean to start over, but I renamed my tables to try and make them more clear.
1. Athletes (hasn't changed) and is a parent to..
2. Assessment Battery - This is a battery of different assessments
3. Assessments - a collection of single tests that can be compiled into a battery
You changed more than the names. Before you had a one to many relationship from the second table to the third. Now you have many to one and the fields used as match fields have also been changed. Please note that I was not suggesting that you make such a change. As I understand it, you have many measurements to record for a given Assessement 9using your latest post when I use this name) and each of these would be a different record in Assessments.
ahhh. Sorry I set it up as you explained in the previous post. Just re-read it and thats just how you were imagining it. I put it back, I think how it should be with The athlete parent to the battery and the battery parent to the assessment. the score is in the assessment table and not the battery.
To recap because I changed the names all around. Statements are:
1. An athlete can have many batteries
2. A battery can contain many assessments
3. An assessment is a single measureable event