473,322 Members | 1,347 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Creating dates: for attendance register

8
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
Jan 6 '09 #1
5 8886
NeoPa
32,556 Expert Mod 16PB
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 "application" 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?".
Jan 7 '09 #2
Lindie
8
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....Lindie
Jan 9 '09 #3
NeoPa
32,556 Expert Mod 16PB
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.
Jan 10 '09 #4
Stewart Ross
2,545 Expert Mod 2GB
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
Jan 10 '09 #5
Lindie
8
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
Jan 10 '09 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: donnie_darko | last post by:
Hi there, I'm studying DBs again after a summer break and have forgotten some basics. I have 2 tables that I need to put into a DB but I'm not sure how I should do it. Here are the table names...
4
by: spnz via AccessMonster.com | last post by:
Hi there I am hoping for a little bit of advice on creating an attendance report. I work for a company that employees a large number of temp staff and I need to monitor their attendance. I...
5
by: John | last post by:
I have 2 tables, one with dates and information about those dates, and one with people information. I want to create a report listing each date and the people who attended on that date (who have...
0
by: shun | last post by:
Hello I strated a project "attendance register". consiists of user login and logintime and logout time using ASP.net. can any one help me, or any sites which have free source code about this....
9
by: Greg | last post by:
Hi, I have a table with "dates", i'd like to display those dates on a calendar. I've put a calendar in a form, linked to my "date" field, and it works, but only showing one "date" per calendar....
1
by: Shun | last post by:
Hello, My requirement is to develop an Employee Attendance Register. For ex: I am in some X country and my office is in Y country. now my requirement is i want to check the employees present...
0
by: spattarkine | last post by:
How to create daily attendance system for students which create warning after each 3 absents.idea is as soon as the class and section are selected,names and roll no of all the students of that class...
1
by: Dale | last post by:
Access 2003 I am trying to figure out how to develop a report that will display students in 1st column, dates across the top, and "P" or "A" for the data. I have a crosstab that displays what I...
1
by: aajayaprakash | last post by:
Am so glad someone atleast wroteback ! I am trying to set up an attendance register where time someone comes into an office and leaves is recorded...a php based timecard/punchcard kind of thing. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.