Reports- looping through records (1 Viewer)

[litrainer]

I run a monthly attendance report. It is grouped by staff, then child and finally service. Nothing fancy.

The detail section is a list of dates the child attended and the # of units earned. Again, nothing fancy.

The requirement is now to have the attendance records print out in a table format labeled with the rows labeled 1 to 31 (for each day of the month) If the child had attendance on 4/3, 4/19 and 4/22 then the #3 box would have the date and the number of units. The #19 and #22 box would have data, all other boxes are blank.

I'm open as to method. I realize it probably involves recordsets and loops neither of which I know in access

I imagine 31 text boxes each with a for next loop going through the records and stopping when if finds a record for that date or just leaving the box blank when it reaches the end

but I am truly in the weeds and need help

michael

ps- if you want to play the bonus round, there could be more than one record (as many as 4) for the same date

 

[JHB]

Could you show how you want the report and also some sample data, (if you want to attached something, then zip it because you haven't posted 10 post yet)?

 

[litrainer]

thank you for the response. i'm hopefully attaching a zip file, no password. its two pages, the first is the form I'm trying to mimic, the second is the access report i can create. my problem is the attendance; i can out put a list but I can't match up the specific to a specicif point .

the report does not have to print on that form, it has to output looking like that form

thanks again,

michael

 

[JHB]

I would suggest that you use a form with 31 sets of unbound controls.
The 31 set of controls each consist of controls "Start Time", "EndTime", "Unit Code" etc.
The 31 sets of controls are named so that you can use the day in the month to assigned values to the controls:
Example:
"StartTime_01", "EndTime_01", "UnitCode_01", for day number 1
"StartTime_02", "EndTime_02", "UnitCode_02", for day number 2
...
...
"StartTime_31", "EndTime_31", "UnitCode_31" for day number 31
Next, create a record set that retrieves relevant data and Loop through the record set.
Example of code:

  Dim dbs As Database, rst As Recordset
  
  Set dbs = CurrentDb
  'Here you have to change "People" it to your query/table
  Set rst = dbs.OpenRecordset("People")
  If Not rst.EOF Then
    Do
      'Here you have to change control name to yours + the rst field name
      Me("StartTime_" & Format(rst![TheDate], "dd")) = rst![StartTime]
      Me("EndTime_" & Format(rst![TheDate], "dd")) = rst![EndTime]
      Me("UnitCode_" & Format(rst![TheDate], "dd")) = rst![UnitCode]
      rst.MoveNext
    Loop Until rst.EOF
  End If

 

[Meehphill]

thank you everyone