Results 1 to 4 of 4
  1. #1
    smaihlee is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2022
    Posts
    2

    Training Database - Attendance data input form design

    I am setting up a basic database to capture training attendance data. The basic data tables:



    tblSubject - all subjects offered
    tblSession - information for each session we offer
    tblInstructor - list of instructors
    tblAttendance - all registrations for each session, including status field for whether attended or not

    Screen shot included of the relationship view to see all the table fields. I've designed the input forms for the first 3 tables, which is data our team will manually input and update as necessary.

    The Attendance data is where it gets tricky. We use an internal company tool for registrations. After each session we upload Teams attendance data and then the tool allows us to generate an Excel file to show all registrations and whether the person attended or not. This Excel report does not contain any reference to the session (but the team member will know this when they are working with the file). We need to be able to get this data into our database so it can be linked to our session/instructor data.

    Here's where I need help: I want to design a Form(?) that allows the team member to select the target session, then paste the attendance data from the Excel file into a data table at the bottom of the form. Then I want the form to apply the Session ID from the selected session, to the Session ID field in all records of the pasted Attendance data. I would even be OK with uploading the attendance data somehow into the database, as long as we can select the session first then apply the Session ID to the imported records.

    I also attached a screen shots of a sample attendance report we get from our internal tool. Changing the registration tool output is not an option as it's a global tool used by many departments.

    I might have been able to figure this out easily when I was using Access daily (10 years ago), but drawing a blank right now

    Click image for larger version. 

Name:	TrainingDB_Relationships.jpg 
Views:	29 
Size:	44.0 KB 
ID:	48853Click image for larger version. 

Name:	AttendanceToPaste.jpg 
Views:	26 
Size:	57.9 KB 
ID:	48852

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Since the Excel file is consistent and contains the desired data, you can link to it and run an append query that gets the session from your form. You can either link to the file in a static location and always have the user put it there, or use FileDialog to have the user point to the file they want to import. Once linked, you can display the data in the form if desired.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    smaihlee is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2022
    Posts
    2
    Thanks for the reply. I will use those suggestions to proceed.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem, post back if you get stuck.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 04-07-2018, 10:44 AM
  2. Replies: 4
    Last Post: 02-15-2016, 09:34 PM
  3. Attendance Report Design from Excel Data
    By goodguy in forum Reports
    Replies: 3
    Last Post: 02-08-2015, 10:29 AM
  4. Training Database Design Help
    By JGrots in forum Access
    Replies: 18
    Last Post: 02-25-2014, 02:45 PM
  5. staff training data base, training
    By SAJAN in forum Forms
    Replies: 1
    Last Post: 09-22-2012, 05:09 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums