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