Calculating time worked without having an EndTime field

[Nathan-bfhd]

I'm working with data that has a time stamp every time someone has a WorkCode
change or signs out. Sometimes somebody will work in several different
departments in a single day. An example of the data is below

PID Name WorkCode Time TotalTime
TotalDayTime
----------------------------------------------------------------------------------------------
1 Pete Parse Marketing 11/2/2007 7:55:12 AM ????
????
1 Pete Parse Sales 11/2/2007 12:10:28 PM ????
????
1 Pete Parse Log Out 11/2/2007 4:59:38 PM ????
????
2 Angela Ang Marketing 11/2/2007 8:01:23 AM ????
????
2 Angela Ang Log Out 11/2/2007 5:10:45 PM ????
????

All the information I've been able to find on comparing times deals with a
start time and end time in the same record. I don't have that luxury. I do
however have a clear record of when someone starts (earliest time for that
day) and when they stop (time recorded at Log Out). It gets even more
confusing if someone logs in and out several times in the same day. I know
the data is there, I'm just not quite sure how to calculate:
1) time that has passed from the logging of one WorkCode to the next
2) total time worked for a given day per person (PID)

Can someone please help me out here? I have more fields/info available if
something else is needed; I'm just trying to keep my explanation as simple as
possible.

 

[Allen Browne]

Use a subquery to get the next time for the same person, so you can use that
as the TimeOut value.

If subqueries are new, here's a starting point:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[Nathan-bfhd]

Allen,

Thanks for the response. I've done some homework on subqueries and I think
I'm on the right track. I can now get the End Time populated for the lines
that are applicable and I've found a time comparision function that Microsoft
has made available, however, I'm having a problem getting the data to show up
in a report. I think the problem I am having is due to the fact that I don't
have a value in every record for the EndTime. I have set it up that way on
purpose because I don't want every time compared with the next time. I've
tried to give an example below (for the sake of room, I've abbreviated Time
into "T").

PID Name WorkCode Time End T
Elapsed T
------------------------------------------------------------------------------------
1 Pete Parse Marketing 11/2/2007 7:55:12 AM next T ????
1 Pete Parse Sales 11/2/2007 10:10:28 AM next T ????
1 Pete Parse Log Out 11/2/2007 11:59:38 AM ????
1 Pete Parse Marketing 11/2/2007 1:01:23 PM next T ????
1 Pete Parse Log Out 11/2/2007 5:10:45 PM ????

For instance, if someone logs out at lunch time, I don't want the time he is
on lunch to be counted toward the workday. I have been able to successfully
get the data I want out of a subquery with specific criteria, and I've tested
the Microsoft function I'm using with a test Table, but I'm still getting an
error on my report when I try to put everything together -- "Invalid use of
Null." I'm guessing it is because some of the "EndTimes" are null. I have
tried a few things to get around this issue, but I'm not as familiar with
coding reports as I am forms, and each idea has been met with failure.

Do you have any ideas of how I can get the information I'm looking for into
a report?

 

[Nathan-bfhd]

I just found out that every record in my EndTime field is blank. I guess
this is where I should back up a little and explain a little about the course
I've taken.

I initially ran into the "Multi-level Group By not allowed" error when I
attempted to build my report off of the query I built the subquery in. I
found the 4 suggested work-arounds on your (Allenbrowne.com/subquery-02.html)
website. The first is not practical for me. The second seemed like it would
logically work, however, when I unchecked the "show" box, the entire field
would disappear as soon as I closed the query.?! Obviously, without the
field/subquery in existance, there is no way for me to use it in a
report...or anywhere for that matter (any idea why the field disappeared?).
The language in the 3rd work-around didn't make sense to me -- it seemed like
it contradicted itself. The 4th option sounded quite complex.

So, I decided to try building a new query based on the query with my
subquery in it. I included all the fields I needed in my report and switched
the record source from my original query to the new one and it opened without
an error. I thought it was passing all the information I needed (since the
query itself displays all the data, including the subquery data), but as I
just found out by placing a text box in the report to display the EndTime, no
data is being passed to it from the new query.

So, I guess I'm back to trying to get around the "Multi-level Group By not
allowed" error.

Any help by anyone would be greatly appreciated.

Nathan

 

[Allen Browne]

Ah: This query is aimed for a report.

A straight-forward (though inefficient) solution might be to use DMin()
instead of the subquery. This would select the lowest date/time value for
the same person that is a LogOut. Those criteria go in the 3rd argument of
DMin(). This should avoid the "multi-level group-by" problem.

The other alternative is probably the 4th one. It's not that complicated.
Basically you set up a table with the data displayed as you want (i.e.
log-in and log-out fields.) When you need to run the report, you run code
to:
a) Delete all records from the table.
b) Populate it with the query that contains the subquery to get the log-out
time.
c) Open the report, which is bound to this temporary table.

Post back if you need further help on either approach.

 

[Nathan-bfhd]

I tried to us the DMin/DMax functions to get around the "multi-level
group-by" error, but I can't figure out how to get the same data without
running a query of some sort. There are several comparisons and criteria
that have to be met. I've included below the subquery I used to get the
information I need. Maybe seeing what I'm doing will help clear something
up.

(SELECT TOP 1 [tbl_NextTime]![LogTime] + [tbl_NextTime]![LogDate] FROM
Personnel AS tbl_NextTime WHERE tbl_NextTime!PersonnelID =
Personnel.PersonnelID AND tbl_NextTime!LogTime > Personnel.LogTime AND
tbl_NextTime.LogDate = Personnel.LogDate AND Positions.PositionsID <> 20
ORDER BY tbl_NextTime!LogTime ASC, tbl_NextTime.PersonnelID) AS NextTime

The PositionID 20 is the Logout position. I was not able to figure out how
to get the above info into the DMin/DMax function because I have to compare
data against itself. At this point, it looks like I'm going to have to do
the temporary table thing. Please explain further on this option.

Where do I put the code that erases the table records and populates the
table from the query? Can I put that code in the On Open event of the Report
or am I going to have to create a form interface for this? I've never had to
deal with a temporary table before so I'm unsure of the code used to delete
and populate. Thanks again.

 

[Allen Browne]

The DMin() could use a query as its domain if necessary.

The code could go in the Open event of the report if users open the report
directly from the Database Window/NavPane. If you provide an interface with
a command button that opens the report, you could use the Click event of the
command button.

Just to be clear, deleting the records from Table1 would be just one line:
dbEngine(0)(0).Execute "DELETE FROM Table1;", dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I tried to us the DMin/DMax functions to get around the "multi-level
group-by" error, but I can't figure out how to get the same data without
running a query of some sort. There are several comparisons and criteria
that have to be met. I've included below the subquery I used to get the
information I need. Maybe seeing what I'm doing will help clear something
up.

(SELECT TOP 1 [tbl_NextTime]![LogTime] + [tbl_NextTime]![LogDate] FROM
Personnel AS tbl_NextTime WHERE tbl_NextTime!PersonnelID =
Personnel.PersonnelID AND tbl_NextTime!LogTime > Personnel.LogTime AND
tbl_NextTime.LogDate = Personnel.LogDate AND Positions.PositionsID <> 20
ORDER BY tbl_NextTime!LogTime ASC, tbl_NextTime.PersonnelID) AS NextTime

The PositionID 20 is the Logout position. I was not able to figure out
how
to get the above info into the DMin/DMax function because I have to
compare
data against itself. At this point, it looks like I'm going to have to do
the temporary table thing. Please explain further on this option.

Where do I put the code that erases the table records and populates the
table from the query? Can I put that code in the On Open event of the
Report
or am I going to have to create a form interface for this? I've never had
to
deal with a temporary table before so I'm unsure of the code used to
delete
and populate. Thanks again.

Ah: This query is aimed for a report.

A straight-forward (though inefficient) solution might be to use DMin()
instead of the subquery. This would select the lowest date/time value for
the same person that is a LogOut. Those criteria go in the 3rd argument
of
DMin(). This should avoid the "multi-level group-by" problem.

The other alternative is probably the 4th one. It's not that complicated.
Basically you set up a table with the data displayed as you want (i.e.
log-in and log-out fields.) When you need to run the report, you run code
to:
a) Delete all records from the table.
b) Populate it with the query that contains the subquery to get the
log-out
time.
c) Open the report, which is bound to this temporary table.

Post back if you need further help on either approach.