Re: find missing time periods

Tech-Archive recommends: Fix windows errors by optimizing your registry

The Time Periods table is set up with 2 fields:
[Period], Number, primary key
[Start Time], Date/Time

table data:
period Start Time
1 7:00:00 AM
2 7:30:00 AM
3 8:00:00 AM
etc in 1/2 hour increments to period 21 at 5:00:00 PM
and Yes it is an enumerated table with all possible times periods throughout
the day.

The system that records call statistics uses these same 1/2 increments for
recording number of calls a worker took as well as which 1/2 hour they
clocked in. It's not perfect but the DB was developed around its
idiosyncrasies. :-)

The Splits table has numerous fields related to call stats. I'll give a few
for illustrative purposes in case it helps give an idea of the info. I only
need to utilize Date and Login for this particular query. Table contains no
primary key.
[Date], Date/Time
[Login], Date/Time
[Split Skill], Text
[ACD Calls], Number (# of calls in each 1/2 hour period)
[ACD Time], Number (time of call recorded in seconds)

some table data:
9/1/2006 10:30:00 AM Operations 1 120
9/1/2006 10:30:00 AM Service Desk 1 45
9/1/2006 11:00:00 AM Operations 0 0
9/1/2006 11:00:00 AM Service Desk 0 0
9/5/2006 12:00:00 PM Operations 1 55
9/5/2006 12:00:00PM Service Desk 1 45

The preliminary query groups the data by date and time period.
SELECT Splits.Date, [Time Periods].Period, Splits.Login
FROM [Time Periods] INNER JOIN Splits ON [Time Periods].[Start Time] =
Splits.Login
GROUP BY Splits.Date, [Time Periods].Period, Splits.Login;

What my "unmatched" query should/needs to return is those time peroids each
day that are missing (i.e. worker was not clocked in). For example, I should
have the following time periods for 9/1 because this worker, who starts at
8:00, came in late:

9/1 8:00
9/1 8:30
9/1 9:00
9/1 9:30
9/1 10:00

Hope the explanation answers your questions and helps clear up, instead of
cause more, confusion.

Thanks,
Marcia


"Gary Walter" wrote:


"Office User" wrote:
I need to find gaps in a series of time periods (ie 8-8:30am or
10-11:00am).
I have a table with the Time Periods listed as well as a query with each
workers date and times for each month. The Unmatched Query won't work
because one day the worker may be "clocked out" from 8-8:30 but another
day
may be "clocked out" from 10-11:00.

Here's the SQL of the Unmatched Query I attempted (in case it helps with
field names, etc)

SELECT [Time Periods].Period, [Time Periods].[Start Time]
FROM [Time Periods] LEFT JOIN [Grouped by Date & Time] ON [Time
Periods].[Start Time] = [Grouped by Date & Time].Login
WHERE ((([Grouped by Date & Time].Login) Is Null));

Any insight would be greatly appreciated.
Marcia

Well...you said "*any* insight"....

There are so many ways someone might model
time periods, so please start with describing the
original table that fuels the "query with each workers
date and times for each month." Please give the table
name(s), field names and types, plus some sample
data (which may be bogus, but illustrates what a
"gap" is). Then, please provide the SQL for this
preliminary query ([Grouped by Date & Time])
and results for your sample data.

Then, please present the results you expect from
your unmatched query using your sample data....
make sure your sample data illustrates how "clocked
out" at different times (that caused your unmatched
query not to "work") should work...

{BTW, it is never a good idea to include any punctuation
in an object name, especially a char that is also an operator
like "&". Also, somewhere down the line you may realize
that using spaces in your names just makes bracketing a
pain in the petui and does not really make your model any
more "readable." For example, "GrpByDateTime" would
be a start (although it hardly tells someone what the query
does).}

So...why is the modeling important?

A) A date/time field serves best as a point in time.

B) Elapsed time (gap/period) typically works best as a scalar
(i.e., Long or Integer) where you work with a unit of time
(like number of seconds).

For example, you might have "modeled" your Login as
a Date/Time (which makes sense to me...you are storing
a point in time). Whether you store the date *and the time*
or just the time (so date part will be 12/30/1899), the time
portion will be a fraction of 24 hours and computers have
trouble "matching" fractions (as in equalities of JOINs or
WHERE clauses).

It *might* be that your query simply fails in that respect,
and could be corrected by changing the ON clause to
something like

ON
Format([StartTime],"hh:nn:ss AMPM")
= Format([LogIn],"hh:nn:ss AMPM")

or, if fields contain date as well....

ON
Format([StartTime],"mm\-dd\-yyyy hh:nn:ss AMPM")
= Format([LogIn],"mm\-dd\-yyyy hh:nn:ss AMPM")

I'm assuming that table Time Periods was an
enumeration of all possible Start Time's. If so,
it would be easy enough to add one more field
(say "strStartTime"), run an update query that
fills in this field...

UPDATE
[Time Periods]
SET strStartTime =
Format([StartTime],"hh:nn:ss AMPM");

or (if need date as well)

UPDATE
[Time Periods]
SET strStartTime =
Format([StartTime],"mm\-dd\-yyyy hh:nn:ss AMPM");

then, in your "GrpByDateTime" compute
an extra field for a string LogIn (say "strLogIn")

Field: strLogIn: Format([LogIn],"hh:nn:ss AMPM")
Table:
Sort:
Show: <checked>
Criteria:
Or:

then, your unmatched query would join on
the 2 string fields....

===============================
another possible way would depend on how
your [Login] is determined...

if a login is always entered in specific increments of the
hour, i.e., 8:00, 8:15, 8:30, 8:45, 9:00, 9:15,...

then, you can compute the "number of increments"
since midnight for each Login....

then you probably would not even need your enumerated
table (if that is what [Time Periods] is)....

here is where I usually give a solution that typically
ends up having nothing to do with your problem...

so, it would be better if you first reply with requested
info from above...




.

Relevant Pages

  • Re: find missing time periods
    ... User and UserShiftStart in sample data, ... I believe your unmatched query actually ... FROM [Time Periods], Splits; ...
    (microsoft.public.access.queries)
  • RE: detect overlapping time periods
    ... > union query. ... > Dim dblStart As Double, lStarts As Long, dblClipLo As Double, dblClipHi As ... >> I need to detect if two time periods overlap. ...
    (microsoft.public.access.modulesdaovba)
  • Re: find missing time periods
    ... I have a table with the Time Periods listed as well as a query with each ... The Unmatched Query won't work ... you might have "modeled" your Login as ...
    (microsoft.public.access.queries)
  • Re: Crosstab Query II
    ... The query result shows all 1/4 hour time periods from 8AM ... The final query data would give me all customers ... >-your current SQL view of the Crosstab ...
    (microsoft.public.access.queries)
  • Re: Year-to-date Totals
    ... You have a query that ... you want to get totals from for various time periods time. ... >> Joe Cilinceon ...
    (microsoft.public.access.queries)