Creating dates: for attendance register

[Lindie]

We use a spreadsheet to record attendance for pupils with special needs. They live-in so we need 365/365 attendance details. The spreadsheet is getting too cumbersome, a Db is needed.

I would like to turn the spreadsheet into a database. In the spreadsheet we have every day listed and then simply enter an attendance code.

for the Db I have 3 tables StudentDetails Attendance TeacherDetails

I use a StudentID as the primary key in both StudentDetails and Attendance

What I cannot work out is how to get the equivalent to our spreadsheet calendar.
I guess that one does not create these in a form but that one uses some clever VBA.

How do I achieve this, presuming there is a solution?
Happy 2009 Lindie

[NeoPa]

That really depends on exactly what you want to use the "Calendar" for.

Some date related things are very straightforward . Some others can be a real head-ache.

Often, when transferring an "applicatio n" from one form to another (Excel to Access for instance) it is a good idea to ask your questions from a lower perspective. Instead of "What is the Access equivalent of this item I have in Excel?" you should consider asking "What is it exactly that I need my Access database system to do for me?" followed by "How do I get my database system to do that which I need?".

[Lindie]

That's good advice; well I needs dates against attendance 365/365.
For any one date staff will enter attendance codes against each individual.
I envisage a form looking like a spreadsheet as staff are used to that.
What I feared I would have to do is to create 12 forms, one per month, with all the dates on it.
That does not feel right though.
I have some VBA code for Excel that creates a spreadsheet with a month's worth of dates. I don't think that wil work in Access hence I wonder whether there is something like that for Access too.

Still wondering....Li ndie

[NeoPa]

While this may be possible Lindie, it is pretty complex stuff. I wouldn't attempt it unless there were a very compelling reason. I know it would be letting me in for a whole heap of pain. It is possibly even more severe a situation for you, as I'm guessing you probably don't have as many years experience to draw on as I do.

This is one of those situations where it is actually a lot more natural to do something like this in Excel.

Having said that, there are simpler, if less obviously recognisable ways to take in the data. A calendar control for instance, could be used to select the date for each entry. Displaying your info in a formatted report (by day within discreet months) will prove difficult though.

It's hard to consider a project design at a distance as there are so many details I can't know. Besides, it could not properly be designed without an understanding of your capabilities being included the equation.

I suppose, in short, what you are looking at is not in the least straightforward .

[Stewart Ross]

Hi. Whilst your particular situation is not identical to a school or college - as these do not have 365 day attendance - if you look at what commercial student records systems do you will see that this apparently simple task is fairly involved.

They start by recording the institution's academic years and their start and end dates, then in a linked one-to-many table the calendared attendance weeks for the academic year (so that there is base information on which weeks students should potentially be in attendance). There is also usually a semester or block pattern which groups the institutions study year into blocks - Semester 1, Semester 2 etc.

For each course in the system it is then possible to set its attendance pattern based on the defined weeks available for the institution. The course weeks are a subset of the potential weeks and blocks - but they can be different depending on the particular course and its requirements.

In a school environment where overall attendance is recorded there may be no need to record subject-level attendance weeks. In colleges and universities the subjects are set up with their own attendance patterns, as subsets of the course attendance pattern. In a school that does not need to record courses as such there would still be a year-group pattern, with each year-group reflecting a single student cohort.

When it comes to recording individual student attendance it is done either by creating records for each student for each potential day of attendance, each potential attendance listed as a single row within a student subject attendance table (typically created automatically by an insert process triggered by staff creating the register), or by using an approach of recording absences instead of attendances. This latter approach greatly simplifies attendance recording, as there are typically far fewer absences to record than attendances. It is still recorded as individual entries for each student in an absence table.

What is important here is that the underlying record structure for recording such data in a relational database is quite different to the grid-based daily register which is a natural means for us to use to record the data in the classroom. Interfacing the two approaches is by no means easy - and NeoPa is right that Excel is more natural in this respect than Access ever could be.

Further, if you think about the tables and relationships actually involved you should see that presence or absence recording is far from trivial. To summarise, the tables involved in attendance recording include:

academic year; (yours may be a calendar year instead)
> year weeks;
> year semester or block;
>> course weeks;
>> subject weeks;
>>> subject teaching class;
>>> student class attendance;

and so on (leaving out other tables such as Course and Cohort, which in any event probably don't apply to your situation).

I would strongly advise you to look at how a commercial system does this task. Although your needs are different, I am sure you have left out potential tables from consideration simply because it will not have been obvious they are needed.

If you can see a commercial system it would help you particularly where a grid-based user interface is provided. In the systems I previously used the printed register for each class was generated in grid format by the system - but the input of attendance data was done on a single-class-instance basis that was not grid based, selecting the date concerned for the class and entering the absence data accordingly.

In your circumstances, taking into account the potential complexities of a relational approach, this is one of the occasions where I think a non-relational Excel-based approach may be simpler, easier and more achievable than an Access one!

-Stewart

[Lindie]

Many thanks NeoPa and Stewart you will have saved me a lot of agony. I quite like tinkering with Dbs on these cold winter evenings but this seems well out of my reach.
We'll stick with Excel.
Lindie