Class attendance report

[Rpschwar]

Trying to create a simple class attendance report. Where the column headers would be class dates and the rows would be students. An "X" in each row/column would show that the students was present in class on that date. Did not think this would be so hard, but can not find any example of how to make it work.

[CJ_London]

data should be stored vertically e.g.

Student..Class..AttendanceDate
Harry.....Woodwork..10/7/2017
John......Woodwork...11/7/2017



then use a crosstab query

row heading - student
column heading - attendancedate
value - "X"

[Rpschwar]

Thanks. That got part of it but I can't get the "x" to appear. This is what my query looks like:
TRANSFORM Count(tmpAttendance.DateEntered) AS CountOfDateEntered
SELECT tmpAttendance.ID
FROM tmpAttendance
GROUP BY tmpAttendance.ID
PIVOT Format([RehearsalDate],"Short Date");

The Transform line is what is wrong. I don't want it to count(dateentered), I want an "x" if date is entered.
I then want to sort the dates in order and be able to enter a date range to create a report.

I'm new to this an would like to send you a screen shot but do not know how.

[Rpschwar]

Finally got the "x" to appear by using:
TRANSFORM IIf(not isnull(Count(tmpAttendance.DateEntered)),"X",Count (tmpAttendance.DateEntered)) AS CountOfDateEntered
SELECT tmpAttendance.ID
FROM tmpAttendance
GROUP BY tmpAttendance.ID
PIVOT Format([RehearsalDate],"Short Date");

but still can't get it to sort by date and to be able to enter a date range to create the report.

[CJ_London]

to enter a date range you need to use parameters (which you can find on the ribbon) - something like

Parameters [Enter Start Date] datetime, [Enter End Date] datetime;
TRANSFORM IIf(not isnull(Count(tmpAttendance.DateEntered)),"X",Count (tmpAttendance.DateEntered)) AS CountOfDateEntered
SELECT tmpAttendance.ID
FROM tmpAttendance
WHERE [RehearsalDate] BETWEEN [Enter Start Date] AND [Enter End Date]
GROUP BY tmpAttendance.ID
PIVOT Format([RehearsalDate],"Short Date");

not sure what you mean by can't get it to sort by date - there is no point sorting on your formatted date because that is now text and will sort on the text order - bring down the rehearsal date group by and sort on it directly - it should then look something like

...
...
GROUP BY tmpAttendance.ID, RehearsalDate
ORDER BY RehearsalDate
PIVOT Format([RehearsalDate],"Short Date");

[Rpschwar]

So here is the problem. I've got the query to look correct with parameters, but how to I create a report that can be dynamic based on the parameters passed?
Here is the first query that takes the parameters for the main table called tblAttendance. I call the query: tmpAttendance3:


SELECT tblAttendance.RehearsalDate, tblAttendance.ID, tblAttendance.DateEntered, tblMembers.FirstName, tblMembers.LastName, tblInstrument.Instrument, tblInstrument.GroupNbr, tblInstrument.OrderNbr
FROM (tblAttendance INNER JOIN tblMembers ON tblAttendance.ID = tblMembers.ID) INNER JOIN tblInstrument ON tblMembers.Instrument = tblInstrument.Instrument
WHERE (((tblAttendance.RehearsalDate) Between #6/1/2016# And #6/1/2017#))
ORDER BY tblAttendance.RehearsalDate DESC , tblInstrument.GroupNbr, tblInstrument.OrderNbr, tblMembers.LastName, tblMembers.FirstName;

I then take that query with the parameters and create a cross tab query called tmpAttendance4_Crosstab:


TRANSFORM IIf(Not IsNull(Count(tmpAttendance3.DateEntered)),"X",Coun t(tmpAttendance3.DateEntered)) AS CountOfDateEntered
SELECT tblInstrument.GroupNbr, tblInstrument.OrderNbr, tmpAttendance3.[Instrument], tmpAttendance3.[LastName], tmpAttendance3.[FirstName]
FROM tmpAttendance3
GROUP BY tmpAttendance3.ID, tmpAttendance3.LastName, tmpAttendance3.FirstName, tmpAttendance3.Instrument, tmpAttendance3.GroupNbr, tmpAttendance3.OrderNbr
ORDER BY tblInstrument.GroupNbr, tblInstrument.OrderNbr, tblMembers.LastName, tblMembers.FirstName
PIVOT Format([RehearsalDate],"yyyy-mm-dd");


I then create a Report based on the tmpAttendance4_Crosstab query. This works fine. But if I want to change the date range I have to recreate both queryies then recreate the report based on the new crosstab query with the new date range.


How can I do this without having to recreate the queries and report each time?

[CJ_London]

you need your headings (column header) to be predefined and neutral. ie. rather than

01/01/2017...02/01/2017 etc

they need to be

day1...day2...day3

[Rpschwar]

But when you run the report what good would it be if the headers where not real dates? How would you do that in the query?

[CJ_London]

in your form or report, you would have some code to 'translate' the heading back into something more informative.

so say your continuous form has a start date control in the header section and you always require the next 14 days - so you will always return 14 columns - day1, day2 etc. and you name your header labels lbl0, lbl1, lbl2 etc

you know day1 equates to the date in your start date control, so a simple bit of code can change the captions

for I=0 to 13
me("lbl" & I).caption=format(dateadd("d",i,[StartDate]),"dd/mm/yyyy")
next I

obviously it gets more complex if you don't want to include weekends for example, but the principle remains the same.

[Rpschwar]

Wouldn't you also have to change the control source for the data that corresponds with the headers? I found some code where it is explained in a little more detail. I'll give it a try and see if it works. Does this look like it is on the right path?
Private Sub Report_Open(Cancel As Integer)
'You didn't know how many columns, or what their names would be, until now.
'Fill in the label captions and control sources.

Dim intColCount As Integer
Dim intControlCount As Integer
Dim i As Integer
Dim StrName As String

On Error Resume Next

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open _
Source:=Me.RecordSource, _
ActiveConnection:=CurrentProject.Connection, _
Options:=adCmdTable

intColCount = rst.Fields.Count
intControlCount = Me.Detail.Controls.Count

If intControlCount < intColCount Then

intColCount = intControlCount

End If

' Fill in information for the necessary controls.
For i = 1 To intColCount
StrName = rst.Fields(i - 1).Name
Me.Controls("lblPgHdr" & i).Caption = StrName
Me.Controls("tbxData" & i).ControlSource = StrName
Me.Controls("tbxSum" & i).ControlSource = _
"=Sum([" & StrName & "])"
Next i
' Hide the extra controls
For i = intColCount + 1 To intControlCount
Me.Controls("tbxData" & i).Visible = False
Me.Controls("lblPgHdr" & i).Visible = False
Me.Controls("tbxSum" & i).Visible = False

Next i

' Close the recordset.

rst.Close
End Sub

[CJ_London]

Wouldn't you also have to change the control source for the data that corresponds with the headers?

providing they are consistent, no.

Note you should specify column headings in your crosstab query - in query design open the properties window if not already open, click on the top part of the window - you will see the column headings property. Populate with

day1, day2, day3....

or whatever you decided to call the columns