I'm trying to tweak my current attendance database for work and make it run quicker for a specific task. I've attached a sample of the functions that I am trying to speed up. Obviously in the sample it runs quick because there is hardly any data for it to search through but when I have it fully loaded with data this particular append query takes hours to run. I need a better solution.
So what I'm trying to do is this....I upload all our employees (115 employees) time clock punches weekly from our payroll system. I then upload all our employees vacation requests and personal time request in there as well. Once all this is uploaded I run the "qryAppendNoShows" query to have it look for employees who did not punch in on a given day. And if they didn't then I have it check those dates against the tblAttendanceEntries to see if they have a scheduled vacation day or personal day for that day. After that I then look at their current work hours in the tblBusinessHours to see if they were scheduled to work on that specific day of the week. Now if they have a scheduled vacation day or personal day then it won't add a record as absent for that day. And if they were not scheduled to work on that day of the week then it won't add a record as well. But if they were scheduled to work that day AND there are no vacation or personal day entries for that day then it adds a record for them being absent that day. There are 2 queries behind the "qryAppendNoShows". the "taNoShow" query looks up in the tblTimeAttendanceEntries to see if there is a day missed in their punches. If it finds a day missing and it's between when their start date was and it's less than the current date then it will show those dates in that query. It matches that against the WorkdaysCalendar table to see if that was an actual workday and not a holiday. The next query is "taNoShowBH". That looks to see if that day, or days is valid based on their work schedule for the week. If it is then those records will show in this query. Then the "qryAppendNoShows" looks to see if there is a vacation day or personal day for that day, or days in the tblAttendanceEntries. If there isn't then it adds an "Unexcused Absence" record for that day, or days. If there is then it doesn't add a record, or records.
The problem I'm having as I said earlier in this post is that it takes hours for this query to run because it's looking through over 60,000 punches for the calendar year for over 100 employees. Then it's looking through 12,000+ attendance entry records. It just takes forever. So what I want to try and do is somehow combine the "taNoShow" query and the "taNoShowBH" query and the "qryAppendNoShows" into one query if possible and speed it up.
The sample DB I've attached has one employee in there as an example. When you run the query "taNoShow" you will see a record for 9/4/14 & 9/5/14 since there were no punches for him for that day. Then when you run the query "taNoShowBH" you will still see those 2 records since he was scheduled to work those days. Then when you run the "qryAppendNoShows" you will only see the 9/5/14 record because he had a vacation day scheduled for 9/4/14. There are other records in the queries as well but these 2 dates show how the example works through all the queries.
Now this was put together about 6 years ago so I'm sure this can be improved on. Any help would be greatly appreciated. I hope I explained it clearly enough but if not feel free to ask any questions.