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]

Re: Query Puzzle Access 2000

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" <mark.reed75@nt lworld.com> wrote in message news:<1V0Zb.113 $aG3.1@newsfep1-gui.server.ntli .net>...[color=blue]
> 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[/color]

[Salad]

Re: Query Puzzle Access 2000

Mark Reed wrote:
[color=blue]
> 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?[/color]

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:Forma t(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]

Re: Query Puzzle Access 2000

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" <oil@vinegar.co m> wrote in message
news:40355E87.4 A1E450@vinegar. com...[color=blue]
> Mark Reed wrote:
>[color=green]
> > Hi all,
> >
> > I have a query at the moment which shows what people have been doing[/color][/color]
on[color=blue][color=green]
> > a certain department throughout an entire shift. It shows the start[/color][/color]
(Min)[color=blue][color=green]
> > time and end (Max) time and then shows total time doing the job. I have[/color][/color]
now[color=blue][color=green]
> > been asked if I can get it to show what they have been doing hour by[/color][/color]
hour.[color=blue][color=green]
> >
> > 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?[/color]
>
> Maybe, maybe not. I will assume that each ctns has a date/time stamp on[/color]
it.[color=blue]
> If so, I would be grouping the values by Login, date, Hour. IOW, add 2[/color]
more[color=blue]
> columns to group by.
> GroupHour:Forma t(DateTimeField ,"h")
>
> The reason to group by date is if people work a second or third shift and[/color]
hours[color=blue]
> go across a day.
>
> The only problem would be the start time for the first record. You could[/color]
group[color=blue]
> also on the Max time and Min time for each hour....I bring this up because[/color]
a[color=blue]
> person may start late, leave early, have a power outage, etc.
>
>
>[/color]