Forum Discussion
Adding a total to a form
I have a very straightforward MS Access database with 1 form. We have a field titled Hours Absent (txtHoursAbsent). I have a key of an id for the participant. So I want to be able to enter th...
I think you need a 2 step process. #1 develop a query that has parameters that filter on 3 fields on your form and returns only the hours for that participant during your reporting period. Something like the below in SQL format
PARAMETERS Forms!frmParticipant!txtParticipantID Int, Forms!frmParticipant!txtReportingPeriodStart DateTime, Forms!frmParticipant!txtReportingPeriodEnd DateTime;
SELECT tblParticipantHours.HoursSpent, tblParticipantHours.ParticipantID, tblParticipantHours.ParticipantDate
FROM tblParticipantHours
WHERE tblParticipantHours.ParticipantID = Forms!frmParticipant!txtParticipantID AND tblParticipantHours.ParticipantDate BETWEEN Forms!frmParticipant!txtReportingPeriodStart AND Forms!frmParticipant!txtReportingPeriodEnd;
Then on your form have an unbound field to display the total hours thus far for your participant. In the ControlSource field use something like
=DLookUp("Sum(qryParticipantHoursPerPeriod.HoursSpent)", "qryParticipantHoursPerPeriod")
PARAMETERS Forms!frmParticipant!txtParticipantID Int, Forms!frmParticipant!txtReportingPeriodStart DateTime, Forms!frmParticipant!txtReportingPeriodEnd DateTime;
SELECT tblParticipantHours.HoursSpent, tblParticipantHours.ParticipantID, tblParticipantHours.ParticipantDate
FROM tblParticipantHours
WHERE tblParticipantHours.ParticipantID = Forms!frmParticipant!txtParticipantID AND tblParticipantHours.ParticipantDate BETWEEN Forms!frmParticipant!txtReportingPeriodStart AND Forms!frmParticipant!txtReportingPeriodEnd;
Then on your form have an unbound field to display the total hours thus far for your participant. In the ControlSource field use something like
=DLookUp("Sum(qryParticipantHoursPerPeriod.HoursSpent)", "qryParticipantHoursPerPeriod")
