Forum Discussion

Mihran65's avatar
Mihran65
Copper Contributor
May 25, 2022

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 the Hours Absent for the reporting period, but show the total hours thus far for the individual.  

 

How can i do this one?  

 

Thanks

 

access
  • MariaBarnes's avatar
    MariaBarnes
    MVP
    May 25, 2022
    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")

Resources