473,320 Members | 1,848 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Validating a list of start & end times

I'm capturing the daily time employees spend on a multiple tasks for
billing. Can someone help me figure out how to confirm that the time
entered by each person daily follows two rules:
(1) no overlaps in time
(2) no gaps in time

good daily entry:
starttime-endtime
8:00 - 8:15 (task 1)
8:15 - 8:45 (task 2)
8:45 - 9:30 (task 3)

bad daily entry:
starttime-endtime
8:00 - 8:15 (task 1)
8:30 - 9:30 (task 2) (bad because of time gap)
9:15 - 10:15 (task 3) (bad because of time overlap)

I'm guessing I can start with a daily query for each employee and
process the data from the query.

I have no idea where to start looking for a function (or save the
function) that looks at one day's data to compare starttime = previous
endtime....

Can someone help please?
Thanks,
Debi

Jun 1 '07 #1
3 2300
On Jun 1, 3:09 pm, debi.sny...@gmail.com wrote:
I'm capturing the daily time employees spend on a multiple tasks for
billing. Can someone help me figure out how to confirm that the time
entered by each person daily follows two rules:
(1) no overlaps in time
(2) no gaps in time
sniped
>
I'm guessing I can start with a daily query for each employee and
process the data from the query.

a subquery will be your friend.

with a table like EmployeeTimes(employeeID, workDate, start, end) try
something like following query:

SELECT employeeID, workDate, start, Times.end,
(Select Max(t.end)
FROM EmployeeTimes T
WHERE T.employeeID = EmployeeTimes.employeeID
AND T.workDate = EmployeeTimes.workDate
AND t.start < EmployeeTimes.start) AS previousEnd,
IIf(EmployeeTimes.start =
Nz([previousEnd],EmployeeTimes.start),"good","bad") AS looks
FROM Times
WHERE workDate = [someDate];

The trick is in 5th column, named previousEnd, that gets the previous
end time (the max(end) less than the current one) in an employee&date
basis.
The 6th column, named "looks" (!), evaluate the subquery results
(actually the previous end time) against the current start time.
Notice that a Nz() function uses the current start time for every
first row in a employee&date set to avoid a bad (null) value.
Hope this (and my English) help.
spier

Jun 1 '07 #2
On Jun 1, 12:18 pm, spier <spie...@yahoo.comwrote:
On Jun 1, 3:09 pm, debi.sny...@gmail.com wrote:
I'm capturing the daily time employees spend on a multiple tasks for
billing. Can someone help me figure out how to confirm that the time
entered by each person daily follows two rules:
(1) no overlaps in time
(2) no gaps in time

sniped
I'm guessing I can start with a daily query for each employee and
process the data from the query.

a subquery will be your friend.

with a table like EmployeeTimes(employeeID, workDate, start, end) try
something like following query:

SELECT employeeID, workDate, start, Times.end,
(Select Max(t.end)
FROM EmployeeTimes T
WHERE T.employeeID = EmployeeTimes.employeeID
AND T.workDate = EmployeeTimes.workDate
AND t.start < EmployeeTimes.start) AS previousEnd,
IIf(EmployeeTimes.start =
Nz([previousEnd],EmployeeTimes.start),"good","bad") AS looks
FROM Times
WHERE workDate = [someDate];

The trick is in 5th column, named previousEnd, that gets the previous
end time (the max(end) less than the current one) in an employee&date
basis.
The 6th column, named "looks" (!), evaluate the subquery results
(actually the previous end time) against the current start time.
Notice that a Nz() function uses the current start time for every
first row in a employee&date set to avoid a bad (null) value.

Hope this (and my English) help.
spier
Thanks! I'll give it a try. I have a table very similar to your
suggestion that contains the values; however, I don't understand two
things in your code...What are "Times.end" and "t.end" ?

Jun 1 '07 #3

debi.sny...@gmail.com wrote:
Thanks! I'll give it a try. I have a table very similar to your
suggestion that contains the values; however, I don't understand two
things in your code...What are "Times.end" and "t.end" ?
Oh, my mistake. I began typing and changed the names in between! sorry
for that.

Times.end should read as EmployeeTimes.end, or simply end, as whe have
only one tabel in the main query level.

Now the T.end thing: as we are querying and subquerying the same
table, we have to tell to access what exactly we are trying! Thus, in
the subquery we have to use an alias to distinguish another instance
of the same table. We got it "renaming the EmployeeTimes table as T in
the the subquery:
...
(Select Max(t.end)
FROM EmployeeTimes T --read "FROM EmployeeTimes _AS_ T"
...
This way we can compare fields from both instances by referencing them
wiaccordingly - T for the inner instance and EmployeeTimes for the
external (main) instance.

Jun 1 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: mike | last post by:
Hello, After trying to validate this page for a couple of days now I was wondering if someone might be able to help me out. Below is a list of snippets where I am having the errors. 1. Line 334,...
3
by: Claire Lescarret | last post by:
Hello, I have devoted my time to write valid html 4.01 strict, and CSS-1 and 2 *but* I also have to use third party's code for webcounter (StatCounter) and Google's SiteSearch, which are...
5
by: DeMZed | last post by:
Hi, When validating my web site, I've got the following error : ------------------------------ Line 31, column 26: cannot generate system identifier for general entity "PHPSESSID" <a...
9
by: Mark | last post by:
Hi there On this page i get some errors when validating: http://www.keyone.nl/lab/beeldlijn/nl/collection.asp The problem is caused by the use of ASP in my pages. This is the code: <a...
2
by: ormy28 | last post by:
I really need some help with the following problem if anyone would be willing. I need a list box to list the opposite of what appears in a query. Heres the details: My database is for a...
0
by: Gary Shell | last post by:
I am experiencing some strange behavior between a UserControl's validating event and a treeview control. Initially, I thought it was related to an issue in the Knowledgebase article 810852...
232
by: robert maas, see http://tinyurl.com/uh3t | last post by:
I'm working on examples of programming in several languages, all (except PHP) running under CGI so that I can show both the source files and the actually running of the examples online. The first...
0
by: Lucilue2003 | last post by:
Hello, I need help validating an xml file against an external DTD. The below code allows me to read the xml file and gather specific elements of interest. If the xml looked like this, How...
3
by: mathieu | last post by:
Hi there, Could someone please suggest an open source implementation of a validating XML parser ? I am interested in how this thing can (should?) be designed. In particular how the errors should...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.