Is this the right approach for an attendance database? (1 Viewer)

[moderatelyclueless]

-Edit in advance: I'm stuck using Access 2003 so I can't look at certain sample databases and maybe don't have access to certain ways of solving problems-

Hi all; I'm just starting to get into the nuts and bolts of Access so I'm worried some of the solutions I think up are needlessly complicated or inefficient because I'm unaware of certain tools or Access way of doing things.

Scenario:
The school director is going to input a date (by default today) and a subform will display all of the students who started before that date and haven't been closed as of that date. Next to each name, there will be 4 radio buttons (present, absent, holiday, hospitalized), which by default will be set to present. The director will go down the line, only needing to click when a student isn't present. At the end, she'll push a save button and the new records will be added.

My proposed method:
1. When the textbox with date input is changed, the subform (or just form and I put the textbox in the header?) will populate itself with a query based on student names whose corresponding startdate and enddate work with the inputted date.
2. The subform will be in continuous view so that it kinda looks like a data sheet but it has radio buttons rather than just spreadsheet cells.
3. When the save button is pushed, some visual basic code in the background will look at record 1, grab the student name and selected radio button value, and add a new record to the AttendanceRecords table with the name, attendance type, and date. Then the code will move to the 2nd record, rinse and repeat until it goes through all of them.

Questions/problems:
1. At its core, is this the Access way of doing something like this? Is there a simpler, more efficient, or generally smarter way of doing attendance?
2. How is step 3 going to work? I can look up how to add new records to a table, but I'm not sure how to move around from record to record collecting corresponding data.
3. Since the AttendanceRecord table uses studentID and typeID rather than actual names and actual attendance types, what's the easiest way of adding the new records with that data efficiently? I'm thinking of making the RecordSource of the subform some sort of linked up set of tables such that if I'm looking at record 1 and the name in the textbox on the subform is "Bob" I can just reference studentID in the background without needing anything on the form at all with studentID?

Thanks in advance. Also if these questions are better suited for another forum, let me know and I'll move it.

 

[CJ_London]

hospitalized? must be a rough school

Looks like you have the right idea although I think you will also need a date field in your attendance table

for your four radio buttons you just need one numeric field (TypeD) with possible values of 0,1,2,3 and defaulting to 0 (=present). in your form you would use an option group based on this field.

To show the student name, you can either use a combo box to show the student name (rowsource something like 'SELECT StudentID, StudentName from tblStudents) with 2 columns, default column =1 and column widths = 0 and probably set the the locked property to yes) or if you want to display the names alphabetically, you'll need to incorporate the student name into your form recordsource and sort it there.

At the end, she'll push a save button and the new records will be added.

To do this you will need substantial code and use what are called disconnected recordsets to populate the form and then act as a source for your update much as you've described. A 'normal' form recordset looks at existing records. Without knowing more about your database, it is not possible to advise further but suggest you google disconnected recordsets.

The alternative (and simpler) way is to create the records in some code in the date afterupdate event which the user then simply edits.

When creating records 'on the fly' like this, you will need to build in some protection against the user choosing the wrong date or a date that has already been entered and also some check that all dates 'to date' have been entered. It may be that the system chooses the next date based on some rule rather than the user entering a date and/or the system checking these entries have not been previously made

 

[moderatelyclueless]

Thanks CJ_London! I ran into a sizeable problem while working on the radio button end of things I was hoping you could help me with:

I currently have 3 tables.
-Student (studentID, name, startdate and enddate)
-AttendanceTypes (attTypeID, attType)
-AttRecord (AttRecordID, studentID, attTypeID, thedate).
I select a date, which leads to a query being run on the Student table and returning only the students whose start/end dates work.

These students are then listed in a subform which is in Continuous View so I have a nice column of student names and attendance radio buttons for each name on the side. The issue is that the radio buttons aren't bound to anything since the type of attendance a student has on a given day is stored in the AttRecords table, not in the Students table which the query is based on.

Since the buttons aren't bound to anything, they're not 50 sets of radio buttons that can temporarily hold the attendance type for each student (for when I later create new records in the AttRecords table), but rather they're just 1 set of radio buttons that all change anytime I click a radio button for any individual student. Here's 2 ideas I have, and I'm not sure which to pursue further:

1. I could add an attendanceType variable to the Students table in order to temporarily store the attendance type (selected via radio buttons), so that the radio buttons are all independent of one another. This feels wrong though since students don't actually have a permanent 'attendance type', so I feel like this is just the sort of convoluted idea a newb would come up with.

2. Instead of creating all those new records in AttRecord after the user selects a date and selects the attendance type for each applicable student, maybe I can create the records (incomplete) right as the user selects the date and the query finds all the students whose start/end dates work. In other words, instead of the subform displaying students names from the query (which comes from the Students table) along with an unbound set of radio buttons, maybe I could have the subform display data directly from the AttRecords table by creating the records with the applicable students and the selected date as soon as the date is selected. That way I could bind the radio buttons to the the actual attendanceType variable in the AttRecords. Does that make sense?

The second scenario seems cleaner/more correct, but I don't know how to go from being able to retrieve a query with only the applicable student names to having X new records in the AttRecords table with the names filled in using that query (and the date filled in using the date they selected, but I think I can figure that part out).