 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2005time tracking
I am having trouble figuring out what to do with this here… We have a web-based form where people enter times for employees that take care of consumers. Most employees have more then one consumer so they have to fill out more then one time sheet to be entered into the web-based form but there are some employees that are putting a time say like below that they would traveling from 2:15 til 3:00 and be at a Consumer’s place from 2:45 til 3:15 which means that they would be in two places at once. Would there be a way I can set up a report or something that would verify that they were not double charging the company? time checking id date | consumerNO |fromTime | toTime | earnCode 1 3/1/2004 12 2:00 pm 2:15 pm Train 1 3/1/2004 12 2:15pm 3:00 pm Trvl 1 3/1/2004 14 2:45 pm 3:15pm Con 1 3/1/2004 14 3:30 pm 3:45 pm Train 1 3/1/2004 667 4:00 pm 4:30pm Trvl Thanks, Jeremy Hi, SELECT a.id, a.date, a.FromTime, a.ToTime,b.FromTime, b.ToTime FROM myTable As a INNER JOIN myTable As b ON a.id=b.id and a.date=b.date and a.FromTime < b.ToTime AND a.ToTime > b.FromTime Basically, there is no overlap if, and only if a.FromTime> b.ToTime ("a" started after the end of "b") OR a.ToTime < b.FromTime ( "a" ended before the start of "b') There is some overlap, partial or complete, in the negation, so, using De Morgan laws, we got the previous formulation. It is generally preferable to keep the date with the time, to avoid problems around midnight. The actual formulation does not handle these cases. In theory, if a overlap b, then b overlap a, so each overlap will be listed twice. To avoid a double listing (of the same problem), you can add a clause like: AND a.primaryKey < b.primaryKey Hoping it may help, Vanderghast, Access MVP >I am having trouble figuring out what to do with this here. > [quoted text clipped - 23 lines] > > Jeremy Hello mike. sorry I didnt reply earlier. It was supposed to notify me when there was a response to my question but that didnt happen. anyways I changed the table so that the time would be in a short time format aka military time. I have the following fields: 'empid' (text format), 'date' (date and time format), 'fromtime' & 'totime' (both are date/time format), and there is a 'id' (which is an long interger field which is just autonumbered when the time is entered) here is the query i modified to use but it just gives me more records then were originally in my table SELECT a.id, a.date, a.FromTime, a.ToTime FROM timecheck AS a INNER JOIN timecheck AS b ON (a.empid=b.empid) AND (a.date=b.date) AND (a.FromTime<b.ToTime) AND (a.ToTime>b.FromTime); Is there something I am doing wrong? It can return the id of the record, the time from and to and the date but if need be can be cut down the the id of the record. Thanks for the help so far. Jeremy > Hi, > [quoted text clipped - 51 lines] > > > > Jeremy HI, Is it possible that a given interval overlap more than one other interval? You can use a SELECT DISTINCT to remove duplicated selected rows. Hoping it may help, Vanderghast, Access MVP > Hello mike. > sorry I didnt reply earlier. It was supposed to notify me when there was a [quoted text clipped - 86 lines] >> > >> > Jeremy I tried: SELECT DISTINCT a.id, a.date, a.FromTime, a.ToTime, a.empid FROM timecheck AS a INNER JOIN timecheck AS b ON (a.ToTime>b.FromTime) AND (a.FromTime<b.ToTime) AND (a.date=b.date) AND (a.empid=b.empid) ORDER BY a.Date; but it just returned this (just a small example of it) id empid date FromTime ToTime 151720 AdamsR 3/1/2004 15:00 17:30 151725 AdamsR 3/1/2004 9:00 13:00 151721 AdamsR 3/2/2004 15:00 17:30 151726 AdamsR 3/2/2004 9:00 13:00 151722 AdamsR 3/3/2004 15:00 17:30 151727 AdamsR 3/3/2004 9:00 13:00 151728 AdamsR 3/4/2004 9:00 13:00 151723 AdamsR 3/4/2004 15:00 17:30 151724 AdamsR 3/5/2004 15:00 17:30 151729 AdamsR 3/5/2004 9:00 13:00 158939 AdamsR 3/8/2004 9:00 13:00 158944 AdamsR 3/8/2004 15:00 17:30 158945 AdamsR 3/9/2004 15:00 17:30 158940 AdamsR 3/9/2004 9:00 13:00 158941 AdamsR 3/10/2004 9:00 13:00 158946 AdamsR 3/10/2004 15:00 17:30 158942 AdamsR 3/11/2004 9:00 13:00 158947 AdamsR 3/11/2004 15:00 17:30 158943 AdamsR 3/12/2004 9:00 13:00 158948 AdamsR 3/12/2004 15:00 17:30 id empid date FromTime ToTime 166067 Bittin 3/23/2004 8:00 10:45 166068 Bittin 3/23/2004 12:00 14:45 166069 Bittin 3/23/2004 16:00 18:30 166070 Bittin 3/24/2004 8:00 10:45 166071 Bittin 3/24/2004 12:00 14:45 166072 Bittin 3/24/2004 16:00 18:30 166075 Bittin 3/25/2004 16:00 18:30 166074 Bittin 3/25/2004 12:00 14:45 166073 Bittin 3/25/2004 8:00 10:45 > HI, > [quoted text clipped - 96 lines] > >> > > >> > Jeremy Hi, With the following data: timeCheck empID date FromTime ToTime pk 0 2004-03-01 14:00:00 14:15:00 1 0 2004-03-01 14:15:00 15:00:00 2 0 2004-03-01 14:45:00 15:15:00 3 0 2004-03-01 15:30:00 15:45:00 4 0 2004-03-01 16:00:00 16:30:00 5 the query SELECT DISTINCT a.empid, a.date, a.FromTime, a.ToTime, b.FromTime, b.ToTime FROM timeCheck AS a INNER JOIN timeCheck As b ON a.empID=b.EmpID AND a.date=b.date AND a.FromTime < b.ToTime AND a.ToTime > b.FromTime AND a.pk < b.pk returns Query46 empid date a.FromTime a.ToTime b.FromTime b.ToTime 0 2004-03-01 14:15:00 15:00:00 14:45:00 15:15:00 which is the only overlap. Have you a small set of data than can reproduce your problem? Hoping it may help, Vanderghast, Access MVP >I tried: > SELECT DISTINCT a.id, a.date, a.FromTime, a.ToTime, a.empid [quoted text clipped - 146 lines] >> >> > >> >> > Jeremy The query works great thanks so much. I had a table of about 220,000 records and it found 512 errors which is about .23% error ratio not to bad for the first 11 months of the year. Thanks again. But one quick question where you have the a.pk < b.pk would this only look at the records after the a.pk what happens if there is a match that is above a.pk. that wouldnt show it correct or am i missing something? thanks again...major props need to be given for query. it helped tons > Hi, > [quoted text clipped - 178 lines] > >> >> > > >> >> > Jeremy Hi, Yep, you are definitively right... BUT that is NOT a problem if you consider the following: if record "a" overlaps (partially or in full) record "b", then, also, record "b" overlaps record "a". And also, "a" overlaps "a" and "b" overlaps "b". Of these four "mathematically right" result, we are only interested in only "one" meaningful: either one of the first two. Stating a.pk < b.pk just does the trick: it removes the 3 "unwanted" results. Hoping it may help, Vanderghast, Access. > The query works great thanks so much. I had a table of about 220,000 > records [quoted text clipped - 212 lines] >> >> >> > >> >> >> > Jeremy Thanks so much for all the help. It works great and found alot of time overlapping. Thanks again. Jeremy > Hi, > [quoted text clipped - 225 lines] > >> >> >> > > >> >> >> > Jeremy Michel and MVPs, I have the same problem and have tried using your SQL below with no luck. I also posted in response to another string, but haven't got a response, so I'm trying this one which looks to be the same scenario. I have a table with a StartDate and an EndDate. I need a query that will prompt for a date range, and list any record that is ACTIVE during that timeframe, so there will be overlapping. My fields are: StartDate EndDate CR_ID Status The table is linked with Oracle. No date format is shown in Table Design and I cannot edit because of the link. The display is m/d/yy hh:nn, unless the time is midnight, then it's just m/d/yy. Can you help? Thanks! Mary > Hi, > [quoted text clipped - 51 lines] > > > > Jeremy |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.