Solved

Append Query for records that do not exist

Posted on 2014-09-27
5
140 Views
Last Modified: 2014-12-21
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.

Thanks
Larry
Attendance-Sample.zip
0
Comment
Question by:Lawrence Salvucci
  • 3
  • 2
5 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40348061
I'm on my iPad, so I cannot see your data structure, but the first thing I would do is make sure that you indices defined on your date and employeeID fields.  These will be very critical to the speed of this process.

You might also be able to take advantage of a temporary table (see my article for a description of how to do this, along with a procedure I wrote to make the process very easy).  I think I would try to join the Attendance and Schedule tables as the first step, and copy those records where you have Attendance but not Schedule and Schedule but not Attendance into a temporary table.

Once you have that temp table, it should be much easier and quicker to identify those that don't have an approved day off.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 40348894
Hi Dale,
Thanks for the quick response. I think I follow you but not totally. What exactly am I dumping into this temp table? I'm kinda stuck on that question before I attempt to create this.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40348952
Well, I'm still not at my computer, so bear with me.

Again, I would strongly recommend that you create indices in your Attendance and Scheduling table.  Then I would created a "frustrated outer Join" query to identify people on the schedule for the past week (or whatever period) and who don't have records for the corresponding date in the Attendance table.  Something like:

SELECT S.EmployeeID, S.SchedDate
FROM tblSchedule as S
LEFT JOIN tblAttendance as A
ON S.EmployeeID = A.EmployeeID
AND DateValue(S.SchedDate) = DateValue(A.PunchDateTime)
WHERE S.SchedDate BETWEEN #9/22/14# and #9/27/14#
AND A.EmployeeID IS NULL

Because this query does not use a straight [SchedDate] = [PunchDateTime] in the where clause, you will have to edit it in the SQL view.

This will identify individuals in tblSchedule who don't have valid punch times.  Once you have this query, you can change it to a make-table or use the procedure in my temp-tables article to create the temp table (tblUnMatched).  I would expect that this would be a relatively small table, and would then be very easy to compare to your Vacations table, something like:

SELECT UM.EmployeeID, UM.SchedDate
FROM tblUnMatched  as UM <= this is the make table from above
LEFT JOIN  tblVacations as V
ON UM.EmployeeID = V.EmployeeID
AND UM.SchedDate >= V.StartDate
AND UM.SchedDate <= V.EndDate
WHERE V.EmployeeID IS NULL

This should identify those who were scheduled but don't have attendance records, and who don't have approved vacations.  You could then change this from a Select query to an Append query to append these record to your NoShows table.

HTH
Dale
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 40349682
HI Dale,

Ok I created indices and now I'm onto creating the "frustrated outer join". Do I dare ask why you define it as that?
The problem I think I'm going to have with that is that the tblSchedule only has a starting date for their current hours and does not list every date so how would I create this part of the query: "AND DateValue(S.SchedDate) = DateValue(A.PunchDateTime)". When their hours change I enter a new record in that table with a starting date for those hours and then I put an "EndDate" on the prior record to show when those hours stopped. So when I build this query I will need to see where the PunchDateTime falls in between those 2 dates and see what their hours were at that point in time. For their current hours record I leave the "EndDate" field blank to show their current hours. So I'm not sure how I would write that section of the query.

Larry
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 40349756
"frustrated outer join" = the "frustrated" part is that you are looking for records in one, not found (frustrated) in the other.

OK, so the assumption is that each employee with a Schedule.StartDate and a Null in Schedule.EndDate should show up for work every day between two provided dates.  

I keep a table (tbl_Numbers) in every one of my databases.  It contains a single field (intNumber) and 10 records (the values 0 - 9).  With this table and your Schedule table, you can create a query to identify each of the "Employee/Sched Dates" like:

SELECT S.EmployeeID, DateAdd("d", intNumber, #9/22/2014#) as SchedDate
FROM tblSchedules as S, tbl_Numbers
WHERE (S.[StartDate] IS NOT NULL) AND (S.[EndDate] IS NULL)
AND (DateAdd("d", intNumber, #9/22/2014#) >= S.[StartDate])
AND (DateAdd("d", intNumber, #9/22/2014#) < #9/27/2014)

You will obviously need to change those date parameters for each week as you do this processing, but that should be easy enough.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now