2 Attachment(s)
Duplicating a database table
Hello,
I need to duplicate a table that's in a Access database through VB 2008. Allow me to give the run down of what i'm trying to achieve here before I ask for help, as I may not be doing this in the most efficient way possible.
My program is a student attendance logger that allows students to come by, and either scan their ID badge or type their 6 digit code. When they do this, the program will successfully mark them as attending, and also mark the time they signed in on the table. (see photo)
Attachment 126773
My issue is that after the day is over, the data from the past day is still in that table. If the old data remains, there is not a way to keep a daily record of attendance as it will simply be overwritten.
To solve this, my first solution would be to have the program create a new table at startup that includes all the names and ID numbers of the students from the original table, but removes the times they clocked in and their attendance status to "no". That seems like the most redundant way to do it.
My next option would be to use the one table, but somehow have the program remove data from the columns that have the dates in them. and printing out the table at the end of each day. This would be the simplest way of doing it. But I would prefer to do it the first way.
Attachment 126771
Any help is greatly appreciated. If you have ideas on another way I can do this. Please, I am open ears.
Thank you
Re: Duplicating a database table
No, that is noit the most efficient way at all ... what you should have is a table of students, a table of classes, then a table that has student id, class id, and date attended.... so if student A checks in for class 1 on 5/20, it gets written out as a,1,5/20/2015 ... the nthe next day ,same student, same class, different day, it woul;d be a,1,5/21/2015 and so on.
-tg
Re: Duplicating a database table
What you're describing is a pretty standard time logging system. I've seen two basic strategies.
1. Have a table keyed by StudentID and Date with a Time In and Time Out field. You create a record and complete the Time In field on the first swipe of the day and the Time Out field on the second swipe. If you want more than one attendance period in the day (e.g. to allow people to swpe out for lunch) then you need more than one pair of Time In and Time Out fields.
2. Have a table keyed by table keyed by StudentID with DateTime field. Each swipe create a new record and you assume that In and Out are alternating records. I prefer this aproach to the one above because it allows for an indeterminate number of swipes and it allows people to attend over midnight but it is a little hard to maintain.
TG has picked up on the fact that your screen shot has a field called Class in it which you didn't mention in your description. Assuming that's there to record the class they were attending you simply need to add this field as a key on approach 1 or a non key field on approach 2.
Re: Duplicating a database table
Quote:
Originally Posted by
FunkyDexter
1. Have a table keyed by StudentID and Date with a Time In and Time Out field. You create a record and complete the Time In field on the first swipe of the day and the Time Out field on the second swipe. If you want more than one attendance period in the day (e.g. to allow people to swpe out for lunch) then you need more than one pair of Time In and Time Out fields.
2. Have a table keyed by table keyed by StudentID with DateTime field. Each swipe create a new record and you assume that In and Out are alternating records. I prefer this aproach to the one above because it allows for an indeterminate number of swipes and it allows people to attend over midnight but it is a little hard to maintain.
TG has picked up on the fact that your screen shot has a field called Class in it which you didn't mention in your description. Assuming that's there to record the class they were attending you simply need to add this field as a key on approach 1 or a non key field on approach 2.
I understand what you're trying to get at here, but the issue I see with your solution is that it does not allow for the data to be saved. By that I mean saved for an archive of all 180 days of school. Administration requires that all records are kept for the entire year in-case of an incident, and from what I can tell, your method just overwrites the old data. Correct me if i'm wrong. The reason I want to do this the way I do is because I can at least print out a hard copy for each day.
Another aspect of this that I did not note is the "attending" column. This project is mainly to help my professor keep track of her students as our particular class is a mix of 4 classes in 1. So her current attendance system is quite hectic as she has to have about 4 tabs open in her browser at once to log times in an adequate amount of time. The attending column is what will make this easy as she can compare the "class" column to the "attending" column and easily mark students absent that are not present. So, if you know of a way to compare the "students" table and the "ID" table and mark the appropriate students absent on a separate table like TG said, then that may work, as long as I can make sure that "attendance" changes appropriately.
Thanks
Re: Duplicating a database table
Quote:
Originally Posted by
butric
I understand what you're trying to get at here, but the issue I see with your solution is that it does not allow for the data to be saved. By that I mean saved for an archive of all 180 days of school. Administration requires that all records are kept for the entire year in-case of an incident, and from what I can tell, your method just overwrites the old data. Correct me if i'm wrong. The reason I want to do this the way I do is because I can at least print out a hard copy for each day.
Another aspect of this that I did not note is the "attending" column. This project is mainly to help my professor keep track of her students as our particular class is a mix of 4 classes in 1. So her current attendance system is quite hectic as she has to have about 4 tabs open in her browser at once to log times in an adequate amount of time. The attending column is what will make this easy as she can compare the "class" column to the "attending" column and easily mark students absent that are not present. So, if you know of a way to compare the "students" table and the "ID" table and mark the appropriate students absent on a separate table like TG said, then that may work, as long as I can make sure that "attendance" changes appropriately.
Thanks
Ok... you're wrong. ;) :)
Never once did we suggest overwritting data or updating.... but rather INSERT new records each time. So there would be a record of attendance for 10 days ,20 days, 180 days, 300 days, for ever... or at least until the record is deleted.
-tg
Re: Duplicating a database table
I'm not sure where you go the idea that I'd overwrite data from:confused: I never mentioned that. As TG said, this is all about creating new records for each attendance, not overwriting existing ones. All that's really being debated is what the records you create should look like.
Re. the attending column, consider carefully whether you really need it. If you're creating a new record every time a student swipes in then an attendance is signified by the presence of a record for that student. An absence is signified by the absence of a record. Having an attending field simply gives you another piece of data that needs to be maintained, could get out of synch and doesn't convey any additional meaning. If you fnd a record in your database that says a student swiped in at 10AM and out at 3PM but didn't attend... what would that mean? Were they there or not?
I suspect that the idea for an attending column comes from the idea of having a checkbox somewhere in your UI and you want to mirror that into the database but I'd suggest that you "map" the checked state of thecheckbox not to a boolean column in the DB but rather to the existence (or lack of existence) of a rcord in the database.
Re: Duplicating a database table
Ok, Sorry for the confusion.
I'm having a hard time imagining what to do here. I understand the part about creating new records, but does that mean that ALL the info will be in one table? like, a new column for each day of the year?
Re: Duplicating a database table
sigh.... no.... no no no no....
What I would do is have three tables. First would be the Student table... it has their ID, name, and other information. The second would be the Class table... it would have the information about a class, name, teacher, semester, location, etc.... the third table would have four, maybe five fields and track Attendance:
ID, StudentID, ClassID, AttendanceDate
that's it... nothing else. There's no adding columns to anything... you would just simply add a record when ever the student checks into a class.
so if I attend a class today the table would look like this:
Code:
Attendance
------------
ID StudentID ClassID AttendanceDate
1 1 1 2015-21-05
Now, let's say tomorrow, both myself and Funky attend, now the table looks like this: (he checks into the class before I do)
Code:
Attendance
------------
ID StudentID ClassID AttendanceDate
1 1 1 2015-21-05
2 2 1 2015-22-05
3 1 1 2015-22-05
see? there's no new columns being added... only new rows.
Now, tomorrow we have two classes... so now we check into those as well...
Code:
Attendance
------------
ID StudentID ClassID AttendanceDate
1 1 1 2015-21-05
2 2 1 2015-22-05
3 1 1 2015-22-05
4 2 2 2015-22-05
5 1 2 2015-22-05
see how that works?
-tg
Re: Duplicating a database table
^ that +1.
The only thing I'd add is that you said you wanted to record the time they checked in, in which case the AttendanceDate column should be a DateTime rather than just a Date.
Re: Duplicating a database table
Quote:
Originally Posted by
techgnome
sigh.... no.... no no no no....
What I would do is have three tables. First would be the Student table... it has their ID, name, and other information. The second would be the Class table... it would have the information about a class, name, teacher, semester, location, etc.... the third table would have four, maybe five fields and track Attendance:
ID, StudentID, ClassID, AttendanceDate
that's it... nothing else. There's no adding columns to anything... you would just simply add a record when ever the student checks into a class.
so if I attend a class today the table would look like this:
Code:
Attendance
------------
ID StudentID ClassID AttendanceDate
1 1 1 2015-21-05
Now, let's say tomorrow, both myself and Funky attend, now the table looks like this: (he checks into the class before I do)
Code:
Attendance
------------
ID StudentID ClassID AttendanceDate
1 1 1 2015-21-05
2 2 1 2015-22-05
3 1 1 2015-22-05
see? there's no new columns being added... only new rows.
Now, tomorrow we have two classes... so now we check into those as well...
Code:
Attendance
------------
ID StudentID ClassID AttendanceDate
1 1 1 2015-21-05
2 2 1 2015-22-05
3 1 1 2015-22-05
4 2 2 2015-22-05
5 1 2 2015-22-05
see how that works?
-tg
Alright, I understand now. I would like to thank you both on helping me resolve this. And I am sorry for my lack of knowledge of the subject. (Which I guess is why im here.) I will see if this will work with my professor, It looks ok to me.
Thanks again
Re: Duplicating a database table
anybody cares to explain why there is an 'ID' field in that Attendance table ?
Re: Duplicating a database table
Habit... you could also set the StudentID, ClassID, & Date as the PKey ...
-tg
