By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,164 Members | 1,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,164 IT Pros & Developers. It's quick & easy.

Query Puzzle Access 2000

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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.

Nov 12 '05 #3

P: n/a
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.

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.