How do I create a subform/query that list weekly attendance?

[rocklee]

Hi folks,

I'm trying to create a subform that will list weekly attendance for each students of a class.

The previous method was Excel where you list the students by rows and the dates by columns. Dates have to be created weekly until the end of its term.

I would like to replicate that using Access where the dates are created automatically in the subform according to the length of term for each class.

The layout emulates the layout in Excel, would this be possible?

Thanks!

 

[rocklee]

HI guys, I'm still working on this.

Just found out that I can use crosstab querying to list my weekly attendance as columns and users as rows for each class, but this will list every student in every class.

The sql looks like this :

TRANSFORM Count([Activity Details].Activity_Name) AS CountOfActivity_Name
SELECT [Activity Details].Activity_ID, [Activity Sessions].UPN
FROM [Activity Details] INNER JOIN [Activity Sessions] ON [Activity Details].Activity_ID=[Activity Sessions].[Activity ID]
GROUP BY [Activity Details].Activity_ID, [Activity Sessions].UPN
PIVOT [Activity Sessions].[Session Date];

Activity Details - Class name/type
Activity Session - Attendance for the class
UPN - student

The results look like this :

Name 12/05/09 19/05/09 26/05/09
student 1 1 1
student 2 1 1
student 3 1 1
student 4 1 1 1


I can't seem to link the query as a subform to the master form as my Access (2007) doesn't want to allow it. The problem is the columns that are created dynamically, and it will only work with fixed column headings.

I was thinking, could I create a temp table with these data using Access Basic to produce the same result (with each dates as a new column), and then use that to link with the master form?

The other option could be pivot table, but I've no idea how to use them.