JSimoncelli
Registered User.
- Local time
- Today, 09:59
- Joined
- Apr 23, 2012
- Messages
- 73
First off I apologize for the big “wall of text” and I am no expert and self-taught.
I am here not so much with a problem as a question, is the process being done correctly and/or is there a more efficient way of doing it. The process I am using works it just seems clucky.
The attached Access 2013 database deals with training events. I have remove most of the database components that are not relevant to this issue. All of the data is fictitious, names created using a random name generator. All references to training courses, button or links do not work as their underlying queries and tables have been removed.
Training events are events attended by more than one person and an event roster or signup sheet is typically used, in fact I have created a report to generate this signup sheet. Training courses are done by one person, they typically are online courses and a certificate of completion is generated.
Originally I had training events and courses in the same table using the queries and the like. I realized there was an issue, when it came time to enter the attendees into their individual record I had to do it one at a time, and if you had 50/75 or more people in attendance this created a labor/time intensive task.
So I broke the events and courses into two separate tables with their own supporting queries, forms and reports.
Unlike courses, events for the most part don’t change, when they happen and who attended a particular event does. So over a year you could have an event say Fire Extinguisher Training occur several times.
If you look at the Relationship section/tab you will find four tables tblEmployee, tblTrainingEvent, tblTrainngEventLink and tblAttendanceRoster along with the relationships. The objective is to add a record in the tblTrainngEventLink table for each person that attended a give event. I have accomplished this using a macro with a series of update and append queries and a statement that deletes all of the records in the tblAttendanceRoster table. The tblAttendanceRoster table is used as a temporary hold place data until it is appended.
From the Main form (switch board) select the Training Management and Reports button, on this form you will find button related to courses these do not work as their under lying tables and queries were removed. Select the Create/Edit/View Training Event button. This brings you to the Training Event List form.
From the Training Event List form, you can see all of the training events, the event name, type, event date, attendance roster and notes section. Associated with each event you will two buttons, the Attendance Signup Sheet button that generates a signup sheet report containing all of the employees, along with the event information and a box for their initials. The attendance button takes you to the TrainingEvenrt Attendance Roster from. This form contains the event information in the upper section (training event ID is hidden) and a listing of all employees with a check box. Once the event is completed you would use the signup sheet and check off all employees that attended.
Once the boxes are all checked you would select the Process Training Event Roster button, this activates the follow macro.
As you check the boxes to indicate a person has attended a record is added to the tblAttendanceRoster table with employee ID, the first step in the macro is a Requery to ensure everyone is accounted for. Next an update query, this updates the tblAttendanceRoster records with the training event ID. Next an append query takes the Employee_FK and the TrainingEvent_FK and appends them to the tblTrainngEventLink, thus linking all of the employees attending to a given event. The remaining steps remove (delete) all of the records in the tblAttendanceRoster table.
This process works but as you can see it is a bit clunky. Now I know you can turn off the need to acknowledge the update and append queries however as far as I know you can’t do this for the delete actions. I would like a way to have this done “behind” the sign without having to turn off the query acknowledgements. Also more to the point, is the process I am using with the tblAttendanceRoster table being a temporary table the way to go. Is there a better more efficient way.
As stated I am no expert and self-taught,
Thanks in advance.
John
I am here not so much with a problem as a question, is the process being done correctly and/or is there a more efficient way of doing it. The process I am using works it just seems clucky.
The attached Access 2013 database deals with training events. I have remove most of the database components that are not relevant to this issue. All of the data is fictitious, names created using a random name generator. All references to training courses, button or links do not work as their underlying queries and tables have been removed.
Training events are events attended by more than one person and an event roster or signup sheet is typically used, in fact I have created a report to generate this signup sheet. Training courses are done by one person, they typically are online courses and a certificate of completion is generated.
Originally I had training events and courses in the same table using the queries and the like. I realized there was an issue, when it came time to enter the attendees into their individual record I had to do it one at a time, and if you had 50/75 or more people in attendance this created a labor/time intensive task.
So I broke the events and courses into two separate tables with their own supporting queries, forms and reports.
Unlike courses, events for the most part don’t change, when they happen and who attended a particular event does. So over a year you could have an event say Fire Extinguisher Training occur several times.
If you look at the Relationship section/tab you will find four tables tblEmployee, tblTrainingEvent, tblTrainngEventLink and tblAttendanceRoster along with the relationships. The objective is to add a record in the tblTrainngEventLink table for each person that attended a give event. I have accomplished this using a macro with a series of update and append queries and a statement that deletes all of the records in the tblAttendanceRoster table. The tblAttendanceRoster table is used as a temporary hold place data until it is appended.
From the Main form (switch board) select the Training Management and Reports button, on this form you will find button related to courses these do not work as their under lying tables and queries were removed. Select the Create/Edit/View Training Event button. This brings you to the Training Event List form.
From the Training Event List form, you can see all of the training events, the event name, type, event date, attendance roster and notes section. Associated with each event you will two buttons, the Attendance Signup Sheet button that generates a signup sheet report containing all of the employees, along with the event information and a box for their initials. The attendance button takes you to the TrainingEvenrt Attendance Roster from. This form contains the event information in the upper section (training event ID is hidden) and a listing of all employees with a check box. Once the event is completed you would use the signup sheet and check off all employees that attended.
Once the boxes are all checked you would select the Process Training Event Roster button, this activates the follow macro.
As you check the boxes to indicate a person has attended a record is added to the tblAttendanceRoster table with employee ID, the first step in the macro is a Requery to ensure everyone is accounted for. Next an update query, this updates the tblAttendanceRoster records with the training event ID. Next an append query takes the Employee_FK and the TrainingEvent_FK and appends them to the tblTrainngEventLink, thus linking all of the employees attending to a given event. The remaining steps remove (delete) all of the records in the tblAttendanceRoster table.
This process works but as you can see it is a bit clunky. Now I know you can turn off the need to acknowledge the update and append queries however as far as I know you can’t do this for the delete actions. I would like a way to have this done “behind” the sign without having to turn off the query acknowledgements. Also more to the point, is the process I am using with the tblAttendanceRoster table being a temporary table the way to go. Is there a better more efficient way.
As stated I am no expert and self-taught,
Thanks in advance.
John
Attachments
Last edited: