Reporting Recorded Attendance in MS Access
May 10, 2009 9:23 PM Subscribe
I'm an adult education teacher computerizing our attendance recording and reporting using MS Access. The reports need to be laid out in a particular way, and I'm not sure how to make that happen. Can you help me?
I'm all set as far as recording the data goes - there's one table for each student's information, and another for a daily attendance record with the number of hours each student attended in a day, some append queries to add a record for each student for each day, and I edit the attendance record as necessary.
The problem I'm running into is with the reports I need to make. These are reports that we need to give to another organization, and therefore they ideally should look like their own forms. These forms are one page per student and have a calendar-style grid/table view for the attendance, with weekly and then monthly totals for the hours of attendance. I've scanned the form to use as a background image on the reports in Access, but I'm running into problems getting things looking the way I want them to.
The main problem I'm running into is that Access wants to list each day's attendance in its own row, and I'm not sure how to make it display the five days of the week in a horizontal fashion and then go down to the next row, so as to be able to make it look normal on a calendar grid. Can anyone give me any pointers on how to do so?
Also, while I'm asking, can anyone give me any pointers on making the weekly and monthly totals? I haven't yet figured that out.
Information that may be helpful: It's Access 2003, and I'm a relatively advanced computer user, but a relative noob to Microsoft Access. If there are any questions about my question, I'll definitely respond with more clarifying information. If you have any helpful tips, thank you!
I'm all set as far as recording the data goes - there's one table for each student's information, and another for a daily attendance record with the number of hours each student attended in a day, some append queries to add a record for each student for each day, and I edit the attendance record as necessary.
The problem I'm running into is with the reports I need to make. These are reports that we need to give to another organization, and therefore they ideally should look like their own forms. These forms are one page per student and have a calendar-style grid/table view for the attendance, with weekly and then monthly totals for the hours of attendance. I've scanned the form to use as a background image on the reports in Access, but I'm running into problems getting things looking the way I want them to.
The main problem I'm running into is that Access wants to list each day's attendance in its own row, and I'm not sure how to make it display the five days of the week in a horizontal fashion and then go down to the next row, so as to be able to make it look normal on a calendar grid. Can anyone give me any pointers on how to do so?
Also, while I'm asking, can anyone give me any pointers on making the weekly and monthly totals? I haven't yet figured that out.
Information that may be helpful: It's Access 2003, and I'm a relatively advanced computer user, but a relative noob to Microsoft Access. If there are any questions about my question, I'll definitely respond with more clarifying information. If you have any helpful tips, thank you!
This thread is closed to new comments.
You can get monthly totals by once again manipulating the date into a year/month value using the datepart function, but weekly totals might require a custom function, since I don't think there's a function to break out week for you. Then you need to take each of these and write a group by query that will return the monthly and weekly results.
I'm giving you this info off the top of my head since I don't have Access on this PC, but if you combine those tips with some creative web-searching you should be able to figure this out. If you have not had experience with databases you might want to consider learning more about relational theory.
Be aware that with any software like Access there is a right way and there is the brute force method of getting your results. Don't be tempted by the brute force method -- learning the right way will be more satisfying in the end.
posted by SteveInMaine at 3:41 AM on May 11, 2009