If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Overlapping time in Access
Here's the situation, we have a school. Some students are part of a paid work
experience program in another part of the school. Student records are kept in one database. When a student signs in for class, we record time in / time out. When they are on property for work we also include time in / time out. We DONT want anyone to clock in for both at the same time(get credit for 4 hours class during the same time as 4 hours work), but different people input the class time and work time. Is there a macro or query that can make sure that "start time" and "end time" for either work or class is not in between that students "Start time" and "end time" that already exists on the same date? -- Robert C. Melton, Stockton, California |
#2
|
|||
|
|||
Dear Robert:
There's nothing obvious in Access to do this, but it can be done if the database design is executed properly. The details of how to do that in your setup I cannot provide without knowing about your designs in some depth. You'll need the math first. To check for an overlap, we can refer to the data as follows: A1 first record, date/time IN B1 first record, date/time OUT A2 second record, date/time IN B2 second record, date/time OUT There is an overlap if, and only if, A2 lies between A1 and B1 or if B2 lies between A1 and B1. By "between" I mean A2 = A1 AND B1 B2 A1 and = B2 By making this test for every combination in your table, it will also find the converse, where A1 lies between A2 and B2 or B1 lies between A2 and B2. This comparison need only be made within the rows for the same student (obviously). On that basis, a query can be built dependent on how your information is constructed. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Fri, 10 Sep 2004 15:11:05 -0700, "RobertMelton,MOUS" wrote: Here's the situation, we have a school. Some students are part of a paid work experience program in another part of the school. Student records are kept in one database. When a student signs in for class, we record time in / time out. When they are on property for work we also include time in / time out. We DONT want anyone to clock in for both at the same time(get credit for 4 hours class during the same time as 4 hours work), but different people input the class time and work time. Is there a macro or query that can make sure that "start time" and "end time" for either work or class is not in between that students "Start time" and "end time" that already exists on the same date? |
#3
|
|||
|
|||
If you can assume that the "B" (out) time is not before the "A" (in) time,
then the conditions for overlap boil down to: A1 B2 AND B1 A2 "Tom Ellison" wrote in message ... Dear Robert: There's nothing obvious in Access to do this, but it can be done if the database design is executed properly. The details of how to do that in your setup I cannot provide without knowing about your designs in some depth. You'll need the math first. To check for an overlap, we can refer to the data as follows: A1 first record, date/time IN B1 first record, date/time OUT A2 second record, date/time IN B2 second record, date/time OUT There is an overlap if, and only if, A2 lies between A1 and B1 or if B2 lies between A1 and B1. By "between" I mean A2 = A1 AND B1 B2 A1 and = B2 By making this test for every combination in your table, it will also find the converse, where A1 lies between A2 and B2 or B1 lies between A2 and B2. This comparison need only be made within the rows for the same student (obviously). On that basis, a query can be built dependent on how your information is constructed. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Fri, 10 Sep 2004 15:11:05 -0700, "RobertMelton,MOUS" wrote: Here's the situation, we have a school. Some students are part of a paid work experience program in another part of the school. Student records are kept in one database. When a student signs in for class, we record time in / time out. When they are on property for work we also include time in / time out. We DONT want anyone to clock in for both at the same time(get credit for 4 hours class during the same time as 4 hours work), but different people input the class time and work time. Is there a macro or query that can make sure that "start time" and "end time" for either work or class is not in between that students "Start time" and "end time" that already exists on the same date? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Images in a database | Franz | General Discussion | 10 | October 7th, 2004 09:35 AM |
Adding staff photographs to my database | KK | New Users | 2 | September 3rd, 2004 07:41 AM |
Upload Image | Jason MacKenzie | General Discussion | 1 | September 1st, 2004 04:38 AM |
Access License In Terminal Server | JIM.H. | General Discussion | 9 | July 19th, 2004 12:32 AM |
You do not have exclusive access... ERROR | Robin | General Discussion | 1 | July 6th, 2004 01:18 AM |