Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2012
    Posts
    3

    Unanswered: "Check-in" system between Excel and Access

    Hi all, I'm new here to this forum, I stumbled across it while doing some research for the college I work at for a new attendance system they want to use for a weekly event we hold here for the students. I've set up an Excel spreadsheet that will log in the ID number of each student as they scan the bar code on their student ID's and it will also state the time and date that they scanned their ID's. The macro I'm using is:

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
    If .Count > 1 Then Exit Sub
    If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
    Application.EnableEvents = False
    If IsEmpty(.Value) Then
    .Offset(0, 1).ClearContents
    Else
    With .Offset(0, 1)
    .NumberFormat = "dd mmm yyyy hh:mm:ss"
    .Value = Now
    End With
    End If
    Application.EnableEvents = True
    End If
    End With
    End Sub


    What I'm trying to do now is export that sheet into an Access database that contains the names of all the students, and when the data is imported it will match up the student names to their appropriate ID numbers and indicate which students showed up and which students did not.

    My first question is if this is even possible. The Excel spreadsheet is fine but I have no experience with Access. My second question is: what would be a good way of going about matching student names to their ID numbers?

  2. #2
    Join Date
    Jul 2012
    Posts
    3
    By the way, I'm using Office 2007 for all of this.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    wromng way round
    use Access to store / capture the data
    use Excel (if you need to) to manipulate the data
    Access has very powerfull reporting tools but Excel is excellent at ad hoc one off reports
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jul 2012
    Posts
    3
    I thought about that, but the people in the office I'm designing this for want to be able to generate different reports from all of this (i.e. student attendance patterns over the semester), and my boss recommended using Access after Excel seemed to lose its use. We're still using Excel for scan-ins and exports, but that is about it.

    Would it be possible to have a key table with a record of student names and their respective ID's in Access, and when the list is imported, a macro could match the scanned number from Excel and match it to the key table?

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can do what ever you wish, within certain limits

    doing data capture and primary storage in Excel (or any other spreadsheet) is begging for trouble. its single write user, its easy to overwrite data or miss some and not know that you (or who ever comitted the crime its not easy to spot errors or flaws in equations

    once the data is captured in Access it can be exported to excel to allow people to do ad hoc reporting / data analysis. but store the data in A JET database first
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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