These calculations use the Sum function to sum up data in a related table? (or the same table linked in a self join relationship)
If so, you can include another field with the a calculation that returns the year to limit your calculation to all matching records with the same year.
Yes, I had them sum the data in the table and related table.
I had the sum be in the Team table because I figured that is what I was trying to get the amounts for.
So in my team table it looks like this:
Team ID (number, indexed, auto-enter serial)
TeamName (Text, indexed)
AllottedTrainingDays (number, auto-enter Data, Numeric Only, Allow Override) : this is the total amount per Team allowed 26 total training days, this number stays the same each year).
TotalDaysTrained (Calculation, unstored Sum(CourseTbl::Days;))
TotalRemainingTrainingDays (Calculation, unstored, = AllottedTrainingDays - TotalDaysTrained)
How do I do the other calculation based on the year. I am still learning how to use more than one relationship to do calculations and what not. I am used to using Access and doing querys so this is all new and confusing to me.
I've developed in Access also and created quite a few forms and reports that relied on manipulted SQL expressions to pull up the right recordset, so feel free to use those terms to describe what you need if that make's it easier.
What relationship have you defined between Team and CourseTbl?
I think you have:
Team::Team ID = CourseTbl::Team ID
You need to add a calculation field like this to CourseTbl: cYear = Year ( StartDate )
Add a number field in team: Year
Now modify your relationship to be:
Team::Team ID = CourseTbl::Team ID AND
Team::Year = CourseTbl::cYear
(To add more fields to an existing relationship, double click the relationship line in Manage | Database | Relationships)
Now enter 2010 in Team::Year to see your sum function compute the total days for 2010. Change it to 2009 and you'll see the total for 2009.
Note if a training season starts in one calendar year and ends in the next, you'll need a more sophisticated calculation to identify the training season correctly.
I started to implement you solution. I am not that great with Access either which is why I though Filemaker would be easier but (I am still learning how the relationships work in Filemaker). Here is what I have as far as table structure.
I have a Teamtbl (contains, TeamID, TeamName, AllottedTrainingDays, TotalDaysTrained, TotalRemainingTrainingDays, and now Year)
Traineetbl (contains, TraineeID, FirstName, LastName, Prefix_suffix, TeamID, and a calculated field for FullName) This table is linked to the TeamTbl via TeamID
Course_Traineetbl this is my join table (contains, Course_TraineeID my primarykey, CourseID, TraineeID) This table is joined to my Traineetbl via TraineeID and is also joined to my CourseTbl via CourseID.
Coursetbl (contains CourseID, CourseTitle, StartDate, EndDate, Notes, CourseType, DaysofCourse, and now per your suggestion CYear)
Will your solution work with the way that my tables are setup with having the Course_TraineeTbl in there. The only part I dont see to make it work is I do not have Team ID in the Course Tbl. Do you think I need to add it into that table. Or do you see a flaw in my design.
Note: I tried adding a second TeamTbl in my relationship page, and added CourseID_fk in my Teamtbl and did the relationship as TeamTbl:CourseID = CourseTbl:CourseID and TeamTbl:Year = Coursetbl:cYear but it does not seem to work for me.
That does complicate things quite a bit.
In Manage | Database | Relationships...
Make Year a global field defined in Course_Traineetbl.
Make a new table occurrence of Coursetbl
Link Course_Trainnetbl to Coursetbl 2 by both CourseID and Year.
Define a field in Course_Trainee that uses the sum function to compute the days trained for that trainee for the year entered into the global year field.
In Teamtbl, make your calculation a sum of this new field to get the total training days for the team for the given year.
Since Year is now a global field, you can place it on any layout to select a year and all your records will compute totals based on this value.
I am not sure how to write the sum statement.
I put =Sum (CourseTbl:: Days) in the Course_TraineeTbl and it seems to be summing up the number of training days for that individual but I dont know how to get it to base it off of the year global field.
This is what it looks like in the Course_Traineetbl
DaysTrained....calculation....unstored, =sum (coursetbl::days)
The relationship will filter the values by year as long as you refer to the correct table occurrence in your sum function.
Last Post I said:
"Link Course_Trainneetbl to Coursetbl 2 by both CourseID and Year."
That looks like this:
Course_Trainneetbl::CourseID = Coursetbl 2::CoursID AND
Course_Trainnetbl::Year = Coursetble 2::cYear
Then Sum ( Coursetbl 2::DaysofCourse )
Should compute the total trainee days for this trainee.