Attendance Report

[eckert1961]

Hello,

I am running Access 2003 and I need some assistance with an Attendance Report for a Student database.

I only run classes every Monday, Wednesday and Friday of each month. I want to be able to open the report and have all of the dates for those days of a specific month be inserted into the report. Please reference the attachment for a sample of what the finished report should look like.

Is there a way that this can be accomplished? Any assistance would be greatly appreciated.

Thanks and regards,
Chris

 

[Banana]

You would want to use a crosstab query.

If you go to database windows, then select query tab and click "new", you can select "Crosstab Query Wizard" which will give you some guidance on how you want to organize the students against date.

HTH.

 

[eckert1961]

Thanks for the reply but I don't know if that would give me what I need.

I have the report designed and now I want to fill the column headings with all of the dates of the Monday's, Wednesday's and Friday's for a given month. If the crosstab query is the correct approach could you provide me a pointer that outlines and example?

Chris

 

[Banana]

Well, without knowing a bit about your database, I would have to make an educated guess.

Assuming we have a table for Students and a table for all dates that a class met/will meet on, then third table relating Student to a date (signifying that they did in fact attend this day) and we want to use the crosstab query wizard:

We would create a new standard query that gathers all students and their dates of attendaces (and since we want it by month, we would add a criteria for the dates with "Between #XX/1/XXXX# And #XX/31/XXXX#).

This query then will be the basis of the crosstab query. You would then want to select Student Names as a Row Heading, and Dates as Column Heading, then use First(Student) As Value. This will give you the desired results (you may want to format it a bit but one step at a time).

HTH.

 

[eckert1961]

Hi Banana,

You're partially correct in that I have a table with all of the student's names but I don't have a table of dates that we meet.

Every month we meet on Monday's, Wednesday's and Friday's. I print off the report each month and I take it to class where I manually place a check mark beside each student name that is present.

When I open the report for a given month such as September, then each column heading will have the date for the days that we meet in that month. For September that would be 1, 3, 5, 8, 10, etc to the 29th. I don't want to enter these dates manually, as I do now. I prefer that the dates be automatically entered when I open the report. Any suggestions?

Chris

 

[Banana]

Let me clarify something- Do you want to use the database to record the attendance, or merely print out a report for you to mark attendance by hand, meaning that database has no knowledge of the attendace?

If that is the case, then what I would probably do is make use of unbound textboxes as column heading. The only bound fields would be the student names for a given class. For the textbox to give you date, you would have to use VBA on OnFormat event to supply the date using date functions.

Is that what you were looking for?

 

[eckert1961]

Hi Banana,

You're correct. My database has no knowledge of the attendance. Currently the column headings that have a number in them are unbound textboxes and I want to be able to programmatically fill these with the dates of the days of the week that I noted for a specific month.

I've spent a great deal of time searching this forum and the internet on how to do this but have had no luck so far. Any ideas?

Thanks for replying.

Chris

 

[Banana]

My apologies; the crosstab query suggestion was good only if you were storing attendance information in the database.

But since we only want to automatically fill in dates for a given month, here's a general overview of what we need to do:

1) We need to supply the criteria (say, select month & year) so we get the right month.

2) We would need as many unbound textboxes as possible (since you meet 3 times a week, 15 textboxes should be quite adequate)

3) We would use OnFormat event and do a loop from 1 to 31 with the criteria supplied to form a complete date (e.g. enter Feb 08 in the criteria, then we'd have 2/1/08 to 2/31/08), then for each date, we test if it's a Monday, Wednesday or Friday, and if so, add it to the next available unbound textbox.

Alternatively, we could do a ad hoc query that does the same thing but return only dates that matches the criteria (being within a month, and being a Monday, Wednesday, Friday) and use the recordset within the query to fill in the textboxes.

Either way, we'll be using same functions: Weekday() (If I remember it right as I don't have Access in front of me) which tells you what day of a date is. You can find more information about the function in VBA help files (not Access help files; you'd want to press alt-F11 to open vBA editor then type in the help textbox for that function... the information there is much more comprehensive than in Access help files).


Can you try it out and see if you can make sense out of it?

 

[eckert1961]

Thanks Banana. Unfortunately I'm not sure where to begin. I looked at the Weekday function but I'm not able to figure out the best approach that I should take.

Would you or anyone else have a pointer to something similar?

Thanks,
Chris

 

[Banana]

I'm afraid I don't have Access at home so it won't be until I'm front of my work computer before I can hand you a simple query that should demonstrate how to select all Mondays, Wednesdays and Fridays from query.

 

[eckert1961]

Not a problem Banana as I'm not in a big rush for this.

Thank you.

Chris