Duplicating a database table-VBForums
Results 1 to 12 of 12

Thread: Duplicating a database table

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2015
    Posts
    6

    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)
    Name:  Table 1.jpg
Views: 170
Size:  8.9 KB

    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.
    Name:  Table 2.jpg
Views: 165
Size:  9.7 KB

    Any help is greatly appreciated. If you have ideas on another way I can do this. Please, I am open ears.

    Thank you

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,256

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,513

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  4. #4

    Thread Starter
    New Member
    Join Date
    Feb 2015
    Posts
    6

    Re: Duplicating a database table

    Quote Originally Posted by FunkyDexter View Post
    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

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,256

    Re: Duplicating a database table

    Quote Originally Posted by butric View Post
    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,513

    Re: Duplicating a database table

    I'm not sure where you go the idea that I'd overwrite data from 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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  7. #7

    Thread Starter
    New Member
    Join Date
    Feb 2015
    Posts
    6

    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?

  8. #8
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,256

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,513

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  10. #10

    Thread Starter
    New Member
    Join Date
    Feb 2015
    Posts
    6

    Re: Duplicating a database table

    Quote Originally Posted by techgnome View Post
    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

  11. #11
    Fanatic Member
    Join Date
    Jun 2014
    Posts
    1,001

    Re: Duplicating a database table

    anybody cares to explain why there is an 'ID' field in that Attendance table ?
    do not put off till tomorrow what you can put off forever

  12. #12
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,256

    Re: Duplicating a database table

    Habit... you could also set the StudentID, ClassID, & Date as the PKey ...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.