Here's what I've got:
A database to track employees' planned absences, categorizing each as either "Vacation", "Work Related", or "Appt / Other".
When the user inputs the number of hours of an upcoming absence in to a sharepoint calendar, he or she must choose one of the three aforementioned categories to describe the absence. The "Team Vacation Calendar" table of an Access2007 DB is linked to that Sharepoint list and is updated with all new information entered.
The "qry_VacationSummary" Access query combines data from that "Team Vacation Calendar" table and another table which contains the number of vacation days each employee is allowed annually (tbl_VacationAvailable). "Qry_VacationSummary" also contains expression fields which look at the current date and populate the number of hours for each occurrence if they occurred in the past. Therefore, among the data within "qry_VacationSummary" I have the following 6 fields:
"Vacation Planned", "Vacation Taken"
"Work Related Planned", "Work Related Taken"
"Appt / Other Planned" , "Appt / Other Taken"
And it all works just perfectly.
The "Planned" fields contain a number of hours occurring in the past and in the future. The "Taken" fields contain only the hours occuring in the past.
Then I have a report.
The report is grouped by team, then by employee, then by the three categories of absences ("Planned Absence Type"). The body of the report for each employee contains each absence date and shows the total of those hours which are "Vacation", "Work Related", and "Appt / Other" These values for each of the individual dates on the report are correct.
In each Employee footer, I have totals for all 6 of the "Planned" and "Taken" fields for that employee.
The "Vacation Planned" and "Vacation Taken" totals in the Employee footer are correct. Likewise, the "Appt / Other Taken" and "Work Related Taken" totals fields all to calculate correctly. However, the "Work Related Planned" total seems to contain the sum of both the "Vacation Planned" fields and the "Work Related Planned" fields for that employee, and the "Appt / Other Planned" total seems to contain the sum of all three Planned Absence categories for that employee ("Vacation Planned", "Appt / Other Planned" and "Work Related Planned").
I have copied and pasted these formulas directly from the Control Source property of these unbound text boxes in the footer from the design view of the report:
=Sum([Vacation Planned])
=Sum([Vacation Taken])
=Sum([Work Related Planned])
=Sum([Work Related Taken])
=Sum([Appt / Other Planned])
=Sum([Appt / Other Taken])
It does not appear I have screwed anything up in the formulas, and I can see in both the query I am referencing and in the body entries of the report itself that all the calculations are correct until I get down to these totals fields in the footer of the report.
What in the world could I have crossed, mixed up, overlooks, or forgotten?
Thank you for your help.
~Ray
A database to track employees' planned absences, categorizing each as either "Vacation", "Work Related", or "Appt / Other".
When the user inputs the number of hours of an upcoming absence in to a sharepoint calendar, he or she must choose one of the three aforementioned categories to describe the absence. The "Team Vacation Calendar" table of an Access2007 DB is linked to that Sharepoint list and is updated with all new information entered.
The "qry_VacationSummary" Access query combines data from that "Team Vacation Calendar" table and another table which contains the number of vacation days each employee is allowed annually (tbl_VacationAvailable). "Qry_VacationSummary" also contains expression fields which look at the current date and populate the number of hours for each occurrence if they occurred in the past. Therefore, among the data within "qry_VacationSummary" I have the following 6 fields:
"Vacation Planned", "Vacation Taken"
"Work Related Planned", "Work Related Taken"
"Appt / Other Planned" , "Appt / Other Taken"
And it all works just perfectly.
The "Planned" fields contain a number of hours occurring in the past and in the future. The "Taken" fields contain only the hours occuring in the past.
Then I have a report.
The report is grouped by team, then by employee, then by the three categories of absences ("Planned Absence Type"). The body of the report for each employee contains each absence date and shows the total of those hours which are "Vacation", "Work Related", and "Appt / Other" These values for each of the individual dates on the report are correct.
In each Employee footer, I have totals for all 6 of the "Planned" and "Taken" fields for that employee.
The "Vacation Planned" and "Vacation Taken" totals in the Employee footer are correct. Likewise, the "Appt / Other Taken" and "Work Related Taken" totals fields all to calculate correctly. However, the "Work Related Planned" total seems to contain the sum of both the "Vacation Planned" fields and the "Work Related Planned" fields for that employee, and the "Appt / Other Planned" total seems to contain the sum of all three Planned Absence categories for that employee ("Vacation Planned", "Appt / Other Planned" and "Work Related Planned").
I have copied and pasted these formulas directly from the Control Source property of these unbound text boxes in the footer from the design view of the report:
=Sum([Vacation Planned])
=Sum([Vacation Taken])
=Sum([Work Related Planned])
=Sum([Work Related Taken])
=Sum([Appt / Other Planned])
=Sum([Appt / Other Taken])
It does not appear I have screwed anything up in the formulas, and I can see in both the query I am referencing and in the body entries of the report itself that all the calculations are correct until I get down to these totals fields in the footer of the report.
What in the world could I have crossed, mixed up, overlooks, or forgotten?
Thank you for your help.
~Ray
Last edited:
