Complex Query

[Sean]

I pull data out of my transaction table to determine the number of
transactions a person does per hour. The time stamp within the database I
pull the data from is stored as a text value in the following format,
200701090900. This process works great when I am looking at 1 day, or 1
shift within a day but when I try to look at a weeks worth of data, the data
is inaccurate becuase we only work 8 hours in a day, but the other 16 hrs are
being factired in. Once the table is populated with the raw data I use 2
querys to get the information I need (maybe there is a better way, but...). I
set up the query's to look at the min time versus the max time and again this
works for 1 day. How do I set up a query where I can look at a week or
months worth of data and exclude the hours of the day that a person is not
working?

I would appreciate any assistance I could get on this. Thank you, Sean

Query 1
SELECT Count(PickPack.trans_type) AS NumTxns, PickPack.emp_name AS Name,
Min(DateSerial(Left([date_time],4),Mid([date_time],5,2),Mid([date_time],7,2))+TimeSerial(Mid([date_time],9,2),Mid([date_time],11,2),0))
AS [Min],
Max(DateSerial(Left([date_time],4),Mid([date_time],5,2),Mid([date_time],7,2))+TimeSerial(Mid([date_time],9,2),Mid([date_time],11,2),0)) AS [Max]
FROM PickPack
GROUP BY PickPack.emp_name;

Query 2
SELECT PickPackStep2.Name, PickPackStep2.NumTxns,
(DateDiff("n",[min],[max])/60)-0.5 AS Hours,
[numtxns]/(DateDiff("n",[min],[max])/60) AS TXNsHr
FROM PickPackStep2;

 

[Ron]

Have you tried using the Mid function to breakdown you're date field.
Like Mid(DateField,Start,Length). For example to get the month
Mid(DateFiield,5,2) would return "01". You can concantonate Month & Date to
get your date's you Need.