Help with Queries (Macro)

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
 

Attachments

Last edited:
Yeah, that was probably too much for me to digest. I skimmed it and then looked at your database. I think tblAttendanceRoster is unnecessary. Explain to me what it does. Here's my take:

tblTrainingEventLink holds the people that are supposed to attend specific training.
tblAttendanceRoster holds the people that actually attended specific training.

Correct? If so, tblAttendanceRoster is unnecessary, you simply move the Attended field to tblTrainingEventLink. If not, explain where I went wrong.
 
Perhaps something like this.

tblEmployees
EmployeeID (PK)
Forename
Surname
Email
StartDate
EndDate
ClearDate

NOTE: Active as a Yes/No may be okay, but you can get more information from a Date. For example, if the EndDate has no date then the person is active. But with dates, you can query on when, too.

tblCourses
CourseID (Autonumber, PK)
CourseName
DateAdded
DateExpired

tblVenues
VenueID (Autonumber, PK)
VenueName
DateAdded
DateExpired

tblEventType
EventTypeID (Autonumber, PK)
EventType
DateAdded
DateExpired

tblEvents
EventID (Autonumber, PK)
EventName
EventTypeID (Number, FK to tblEventTypes)
CourseID (Number, FK to tblCourses)
EventStart
EventEnd
VenueID (Number, FK to tblVenues)
DateAdded
DateExpired

NOTE: Added two dates for an event on the offchance you may have an event that spans more than one day.

tblEmployeesToEvents
EventID (Number, PK)
EmployeeID (Number, PK)
Attended (Yes/No)
 
Wow thanks for the quick responses.

@plog

The tblTrainingEventLink is used to identify who Employee_FK attended what even TrainingEvent_FK, that they completed the event.

I needed a way to create a record for each employee in attendance of a given event. This is the tblTrainingEventLink table. To populate this linking table I created the process described. I create the basic event in the Training Event List form (switch boars), save and use the Attendance Signup Sheet button to generate the signup sheet. Once the event is completed the date can be updated and an “empty” Attendance Roster is created by selecting the Attendance button. The button pulls over the information about the event to the Training Event Attenance Roster form.

The upper portion of the form is the event information with the TrainingEvent_ID hidden, the lower half contains a sub form with a list of all employees with a check box.

As you check off the employees that attended, the tblAttendanceRoster table gets populated with the Employee_ID (and some other data not used) the Process Training Event Roster activates the macro that completes the process.

I need the tblAttendanceRoster table to combine the Employee_FK and the TrainingEvent_FK then to append this to the tblTrainingEventLink table to reflect who attended.

Does this help.
 
@Mile-O

Thanks for the quick response.

I like your idea of the Stare and End and clearance dates for the Employee table this will eliminate the Y/N boxes.

The Event Stat and Stop dates will be helpful as well.

I also see you may not be a fan of value lists, I can understand it is kind of the easy way out.. I will have to revisit this.

I don’t see how the DateAdded and DateExpired fields play into this and the tblEmployeeToEvent table I also don’t how this fits in. If it serves the same function as the tblTrainingEventLink table please explain.

Thanks John
 
Does this help.

Not really. You need to put forms and macros and user actions and reports and queries aside for now. Focus on just tables. That's the foundtion of the database, get that right, then move on.

From what you wrote, I believe my assesment of tblAttendanceRoster is correct--it is unnecessary.

I don’t see how the DateAdded and DateExpired fields play into this

You generally don't delete data in a database, you make it so that you can exclude it. Mile-O included these fields so that you could easily identify "Active" data. Suppose a venue gets demolished, going forward, you will no longer use that venue; however for historical purposes you need to retain that data: Thus the Expired field. Same principle for all those other Added/Expired fields. If a record has Expired, it no longer is available to use throughout the database, but it remains in the database so you can run historical reports.
 
I also see you may not be a fan of value lists, I can understand it is kind of the easy way out.. I will have to revisit this
I would only use them on lists that were strictly defined and unlikely to change, such as Months or Days of the Week. Everything else I would drive through a database. This method you can take future development out of the equation by, for example, building an administration form that allows users to add, edit, or delete options from dropdowns (i.e. add a new venue, or event type). Why leave that to the developer? It's a waste of their time.

I don’t see how the DateAdded and DateExpired fields play into this
It doesn't, really. Just a force of habit on my part. I tend to have six fields at the end of my tables called DateCreated, DateUpdated, DateExpired, UserCreated, UserUpdated, and UserExpired. I do this so that I never really delete records, but just flag them up as expired. The DateExpired field also adds to the criteria in my queries, to ensure the dropdowns only have contemporary selections (so John Smith, who left five years ago, isn't still an option).

I update the DateCreated, DateUpdated, UserCreated, and UserUpdated fields when a new record is created in that table. Update the DateUpdated and UserUpdated when a record is edited. And update DateExpired and UserExpired when a record is 'deleted'. Bit of an audit trail, knowing who did what, especially when something goes missing and nobody admits to it. [Plus: what plog says!]

and the tblEmployeeToEvent table I also don’t how this fits in. If it serves the same function as the tblTrainingEventLink table please explain

The way I see it, you have employees and you have events. Therefore, in order to link them you need a junction table. This can hold a key to both Employee and Event tables. Other items pertinent to this 'entity' are whether the employee attended or not.

Effectively, the table acts as your registrar. These are the people you expect to turn up. Then you can say, in that same table, whether they did turn up, hence the Attended yes/no field I suggested. So, what you are trying to do is, to echo plog, creating a redundant table that you are trying to maintain through macros and queries when the reality is, you only need this one table and, at the moment, no macros or queries to get your data recording just right.

Need the data? you can create a query that links Employees to EmployeesToEvents and then to Events. That's the join and usefulness of junction tables.

Also, you'll notice I split out Courses and Events. The Course table defines actual courses - as named things - you will run, such as fire safety, phishing, etc. The events table is seeing these courses as an entity, so the event becomes a one, two, three day affair, and to it you attribute the course, the dates, the employees, the venue, and so on as needed.
 
@plog,
See attached relationship snap shot, I have made the changes to eliminate the Y/N fields adding the recommended date fields.. Thanks

The tblTrainingEvent table are records of individual events that took place at a given time. Over a five year period you could have ten records for Semi-Annual Fire Extinguisher Training, each with a different start/end dates and an attached copy (the physical .pdf of the signup sheet) of the attendance roster/list. The tblTrainingEventLink table could have any number of entries for each instance of a given event, linking each employee attending a give event.

My original question was, what is the best way to populate the tblTrainingEventLink table with employee_ID’s of those that attended.

The query macro on the associated Attendance Roster form along with the tblAttendanceRoster table was my solution.

Is there a better way? I am not an expert in any way, please help me to understand your reasoning.

Thanks
John
 

Attachments

  • RelationshipSnapeShot1.JPG
    RelationshipSnapeShot1.JPG
    53.3 KB · Views: 121
Last edited:
My original question was, what is the best way to populate the tblTrainingEventLink table with employee_ID’s of those that attended.

That all depends on how the data moves from the real world into your database. Is someone keying that in? Or do you get a file that you import that tells you that info?
 
@plog,

First I would like to add I am the designer the user and in some cases the trainer (but I don’t do windows). I created this database for my purposes, with the intent of sharing it to other organization here in central Florida. Once I get the Knicks worked out..

Also if I am not mistaken the Joining table Mile-O mentions and Linking table are the same thing, correct?

Short version: When employees attend an event say Annual Safety Training I generate an Event Attendance Sheet. The employees that attend initial by their names, once completed I use this sheet via the Training Event List form, the query macro and the tblAttenadanceRoster to populate the tblTrainingEventLink (the linking table).

The Attended field in the tblAttenadanceRoster is required, on the Training Event Attendance Roster form there is a sub-form that presents a list of all employees with a check box. As you check the box for each attendee the tblAttenadanceRoster gets populated with the Employee_FK, the TrainingEvent_FK is initially set to 0. In the macro the Update query sets the TrainingEvent_FK to the proper number.

Trying to do this directly to the tblTrainingEventLink table won’t work. Thus the data in the tblAttenadanceRoster table is temporary and deleted once it is appended to the tblTrainingEventLink table.

If you play with the data base you should be able to see my madness…

John
 
Your verbosity is killing me. Am I to understand the answer to my question is:

"I key it in." ?
 
I would have a form for Events, at the bottom of which I would have a subform where I set people who should attend. That subform will allow you to enter people who should attend that event, one of the fields will be a checkbox that denotes if they attended or not. By default and until after the event that checkbox will be false.

After the event when you have your attendance sheet, you open the Event form to that event, go to the bottom and set that check box to true for all of those who attended.
 
Yes, this is exactly (~) how it is set up. The top part of the form has information about the event including TrainingEvent_FK and the bottom is set up as you have suggested.

However the devil is in the details, how would you get the Employee_FK and the TrainingEvent_FK into the Linking table?

My current database does this but I think it can be done better less clucky.
 
The events form is based off of tblTrainingEvent, the subform for the roster is based off of tblTrainingEventLink. You establish a parent/child relationship between the 2 forms using tblTrainingEvent_ID and TrainingEvent_FK. That takes care of getting the TrainingEvent_FK populated.

On the subform you have a drop down which feeds Employee_FK (it saves the Employee_ID, but you can have it also show the employee name). This gets the Employeee_FK populated. No queries/macros needed, just straight data entry.
 
Yes, this works and is pretty straight forward. Do you see a way to do this for more than one at a time to be entered/updated? If I have to do this for 50+ people it going to get old real quick.

I can’t without having some kind of intermediate place to hold the data I.E. the table I was using (tblAttendanceRoster).

Maybe some king of spreadsheet that I can import?
 
Define "this". Do you mean set up the initial roster or marking those who attended?

You're going to have to manually set the initial roster somehow, this form/subform seems the most painless way.

Now to update it with those who attended, you could export the roster to a spreadsheet, include the EventID, EmployeeId, Employee Name and the Attended field (which will be null) in each row, print it off and have attendees initial by their name.

Then after the event you open the spreadsheet and mark the Attended column for those that attended. Import this spreadsheet, and update your Roster table with those that have been marked as attended.
 
I want to thank you for your help with this, I am going to take all of this and mull it around and experiment. I will find something that works probably a combination of all that has been discussed.
Thanks
John
 
Do you see a way to do this for more than one at a time to be entered/updated? If I have to do this for 50+ people it going to get old real quick.

Listbox showing all users and its multi-select property on. Then you can loop through its ItemsSelected property and add users to some other list.
 

Users who are viewing this thread

Back
Top Bottom