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
5 8898 NeoPa 32,564
Recognized Expert Moderator MVP
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?".
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 32,564
Recognized Expert Moderator MVP
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 .
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
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 with their columns:
Student Table:
|
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 currently use excel to manage this job but I am finding it very difficult
to maintain and would really like to move away from excel and have just 1...
|
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 that date under
the date column in their table). Can I do it and how?
Also another problem I'm running into is what if the person is on more
than...
|
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.
Thanks in advance.
regards
Shun
|
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.
I can see all "dates" moving to next records, but one per calendar. :-(
Ex: 17/06 on one claendar, then if i click on next record, I can see my...
| |
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 and also there previous days
attendance(i.e complete Month employee Attendance).
I am farwarding in 2 ways,
1. By searching in Google for any...
|
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 will appear and by clicing the checkbox a student is marked present.At the end of the month the ateendance report should appear like a snapshot of...
|
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 want. I can create a
report that shows the data from the crosstab that looks like what I
want, However, these are "static controls" so when I add...
|
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.
Ive got to get the mysql table to record the time in and time out and the ip address from which the person logged in or logged out each day.
That is...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |