Attendance Record

**timbim** · 06-30-2009, 04:02 PM

I'm looking to produce an attendance record in an access database, I've got one table with the names of the people and another with the dates of the sessions running, and I need a way of entering presence/absence at the sessions, and have a way of entering planned absences ahead of time. If possible, I then need to be able to turn this into a sort of register I can print, showing previous presence, absence and planned absence, and planned absences in the future.

I hope that this is possible, I'm sure it is, and to me it seems to make sense in excel, with the session date as column headers and names in first rows, and then the matrix being the previous records etc. I just need to keep this in access as the names became unworkable in excel. I think the best way of showing the output might be something along the lines of a pivot table, but I'm not sure.

Many thanks,
Tim

**split_atom18** · 06-30-2009, 06:03 PM

There is a ton of questions I could ask.

First of all this is very doable. Which I believe to be your question.

Since you have Access 2007 this is actually quite a bit easier then you think.

You have a table with the dates of the sessions already.

You could add 2 fields to that table.

First being Attended, make it a multivalued field.(only available in access 2007)

Second being ExcusedAbsense, also a multivalued field.

Make a form with Session basically so you can scroll through the sessions. If someone plans on being absent you can just click the check box in the "ExcusedAbsense" field.

Make a couple of queries and reports, and walla you are done.

Hope this helps,

Dan

**timbim** · 07-01-2009, 03:35 AM

Thanks, but I've decided to go down the route of another table with lookups for session date, person and status, and that allows me to attach a note to each incidence, which will be very useful. I've set up some queries, one being a crosstab of session as column headers and name in rows, with the matrix then comprising the status for any given session. However, the headers for session come up as 1, 2, 3 etc rather than a date, which is what I need. Any ideas?

**split_atom18** · 07-01-2009, 11:51 AM

The numbers are the key field for the Sessions yes?

If your lookup looks something like:

SELECT [ID], [Session] FROM [tblSessionList] ORDER BY [ID];

In column widths you would enter in 0";1.5"

Without seeing that is my guess.

Another way to do it might be with a Dlookup.

Hope this helps,

Dan

**timbim** · 07-01-2009, 03:09 PM

I'm not quite sure what you're after there, I'll attach my database to show you. Should make sense, I really appreciate the help on here.

I've now got a query to show each attendance record, and a crosstab query on that should give me what I need, but it doesn't give it by date, it just adds the column header as 1, 2, 3 etc rather than the dates of the session.

**split_atom18** · 07-01-2009, 03:27 PM

Fixed, Very much like I thought.

You had the field storing the "Primary Key" as I poorly worded above. With the field set to 0";1.5". In order for it to show the date in your crosstab query, you needed to add the Sessions Table to the query with a one to many relationship and then put the "Date" field there instead of the session field which is really just a storing of the primary key of the sessions table.

Hope this helps,

Dan

P.S. what branch?, I am a disabled vet of this war(US Navy). Rarely do you see the letters AWOL in a database :P

**timbim** · 07-01-2009, 03:37 PM

This is UK, CCF Navy section. We're a public school, so cadets from 14 to 17, next year (my final year in the section) I'm a PO, head of section, so I'm getting my database sorted out now. What part of US Navy were you?

One thing, though. If I add a new attendance record into the table with a new date, even after a refresh, the new date doesn't show up in the query, but new people do. Do you know the reason?

**split_atom18** · 07-01-2009, 03:57 PM

Originally Posted by timbim

Do you know the reason?

Will look into it.

I was in Submarines.

**split_atom18** · 07-01-2009, 04:04 PM

Originally Posted by timbim

One thing, though. If I add a new attendance record into the table with a new date, even after a refresh, the new date doesn't show up in the query, but new people do. Do you know the reason?

I am a little confused. It is working fine for me. Do you enter in the session data first. Ie the class. Then you go to the attendance table and enter in attendance for that new session and I get the column.

Can I get some more information. Or the database with and explanation of what you are doing?

**timbim** · 07-01-2009, 06:18 PM

It's a strange one. If I add a new attendance record on a new date, it doesn't show in the crosstab query even after a refresh all. If I add a new person, it does. If I enter design view, and then go back to datasheet view for the query, then it updates properly.