Query Puzzle Access 2000

[Mark Reed]

Hi all,

I have a query at the moment which shows what people have been doing on
a certain department throughout an entire shift. It shows the start (Min)
time and end (Max) time and then shows total time doing the job. I have now
been asked if I can get it to show what they have been doing hour by hour.

so the format would need to be something along the lines of :

Date Start End Forname Surname Login Ctns Diff
06/02/04 06:00 06:59 Sam Smith 38965 26 0:59
06/02/04 07:00 07:59 Sam Smith 38965 35 0:59
06/02/04 08:00 08:59 Sam Smith 38965 32 0:59

Can anyone shed any light on the best way of doing this?

Cheers,

Mark

[PaulT]

How about using the query as the record source for a report, and do a
group on the time field by hour?

-Paul

"Mark Reed" <ma*********@ntlworld.com> wrote in message news:<1V*************@newsfep1-gui.server.ntli.net>...

Hi all,

I have a query at the moment which shows what people have been doing on
a certain department throughout an entire shift. It shows the start (Min)
time and end (Max) time and then shows total time doing the job. I have now
been asked if I can get it to show what they have been doing hour by hour.

so the format would need to be something along the lines of :

Date Start End Forname Surname Login Ctns Diff
06/02/04 06:00 06:59 Sam Smith 38965 26 0:59
06/02/04 07:00 07:59 Sam Smith 38965 35 0:59
06/02/04 08:00 08:59 Sam Smith 38965 32 0:59

Can anyone shed any light on the best way of doing this?

Cheers,

Mark

[Salad]

Mark Reed wrote:

Hi all,

I have a query at the moment which shows what people have been doing on
a certain department throughout an entire shift. It shows the start (Min)
time and end (Max) time and then shows total time doing the job. I have now
been asked if I can get it to show what they have been doing hour by hour.

so the format would need to be something along the lines of :

Date Start End Forname Surname Login Ctns Diff
06/02/04 06:00 06:59 Sam Smith 38965 26 0:59
06/02/04 07:00 07:59 Sam Smith 38965 35 0:59
06/02/04 08:00 08:59 Sam Smith 38965 32 0:59

Can anyone shed any light on the best way of doing this?

Maybe, maybe not. I will assume that each ctns has a date/time stamp on it.
If so, I would be grouping the values by Login, date, Hour. IOW, add 2 more
columns to group by.
GroupHour:Format(DateTimeField,"h")

The reason to group by date is if people work a second or third shift and hours
go across a day.

The only problem would be the start time for the first record. You could group
also on the Max time and Min time for each hour....I bring this up because a
person may start late, leave early, have a power outage, etc.

[Mark Reed]

Thanks for the advice,
I have managed to do this by adding the following field to my query
DatePart("h",[time]) AS hr

I have then created another table with 2 fields..... Date part and Time
period.

The data looks something like

Date part Time period
0 00:00 - 01:00
1 01:00 - 02:00

I have then created another query from the first query and the new table,
then linked them by the datepart.

the result gives the a time period of activity along with the start and end
time within that period then the sum of Ctns and the total time worked
within that hour.

Mark

"Salad" <oi*@vinegar.com> wrote in message
news:40**************@vinegar.com...

Mark Reed wrote:

Hi all,

I have a query at the moment which shows what people have been doing on a certain department throughout an entire shift. It shows the start (Min) time and end (Max) time and then shows total time doing the job. I have now been asked if I can get it to show what they have been doing hour by hour.
so the format would need to be something along the lines of :

Date Start End Forname Surname Login Ctns Diff
06/02/04 06:00 06:59 Sam Smith 38965 26 0:59
06/02/04 07:00 07:59 Sam Smith 38965 35 0:59
06/02/04 08:00 08:59 Sam Smith 38965 32 0:59

Can anyone shed any light on the best way of doing this?
Maybe, maybe not. I will assume that each ctns has a date/time stamp on

it. If so, I would be grouping the values by Login, date, Hour. IOW, add 2 more columns to group by.
GroupHour:Format(DateTimeField,"h")

The reason to group by date is if people work a second or third shift and hours go across a day.

The only problem would be the start time for the first record. You could group also on the Max time and Min time for each hour....I bring this up because a person may start late, leave early, have a power outage, etc.