Time Calculation

[Guest]

Hi Folks,

I have a table of starttime and endtime (with calculated daily total) and I
would like to count how many people were at work before 8:30 am, how many
8:30 - 1:30, and finally how many from 1:30 on. Can you suggest how I can do
that?

Thanks in advance,
Bonnie

 

[Guest]

Try this SQL

SELECT IIf([StartTime]<#12/30/1899 8:30:0#,"Before
8:30",IIf([StartTime]>#12/30/1899 13:30:0#,"After 1:30","Between 8:30 and
1:30")) AS TimeFrame, Count(MyTable.StartTime) AS CountמתוךStartTime
FROM MyTable
GROUP BY IIf([StartTime]<#12/30/1899 8:30:0#,"Before
8:30",IIf([StartTime]>#12/30/1899 13:30:0#,"After 1:30","Between 8:30 and
1:30"))

Just Add a where statement

 

[Guest]

Thanks Ofer, I'll give it a try.

Try this SQL

SELECT IIf([StartTime]<#12/30/1899 8:30:0#,"Before
8:30",IIf([StartTime]>#12/30/1899 13:30:0#,"After 1:30","Between 8:30 and
1:30")) AS TimeFrame, Count(MyTable.StartTime) AS CountמתוךStartTime
FROM MyTable
GROUP BY IIf([StartTime]<#12/30/1899 8:30:0#,"Before
8:30",IIf([StartTime]>#12/30/1899 13:30:0#,"After 1:30","Between 8:30 and
1:30"))

Just Add a where statement

Hi Folks,

I have a table of starttime and endtime (with calculated daily total) and I
would like to count how many people were at work before 8:30 am, how many
8:30 - 1:30, and finally how many from 1:30 on. Can you suggest how I can do
that?

Thanks in advance,
Bonnie

 

[Steve Schapel]

Bonnie,

What exactly counts as being "at work"? Do you mean these who start
work during the 3 time periods? Or, let's say for someone who starts at
8:15am, and finishes at 2:00pm, do they count in all 3 groups, since
they were at work for all or part of the 3 periods?

 

[Guest]

Hi Steve,

I don't think I have quite figured out what being "at work" means yet.
Sometimes it means someone is holding up a wall or keeping a seat warm. But
for my programming purposes here it means that they clocked in at one time
and clocked out by another so if they hadn't clocked out yet we will consider
them at work.

Thanks.

 

[Guest]

Hi Ofer,

you SQL works very well thank you. I just have a questions: why can't I
change anything about this (i.e., the line describing the timeframe (Before
8:30) without getting an error?

Thanks.

Try this SQL

SELECT IIf([StartTime]<#12/30/1899 8:30:0#,"Before
8:30",IIf([StartTime]>#12/30/1899 13:30:0#,"After 1:30","Between 8:30 and
1:30")) AS TimeFrame, Count(MyTable.StartTime) AS CountמתוךStartTime
FROM MyTable
GROUP BY IIf([StartTime]<#12/30/1899 8:30:0#,"Before
8:30",IIf([StartTime]>#12/30/1899 13:30:0#,"After 1:30","Between 8:30 and
1:30"))

Just Add a where statement

Hi Folks,

I have a table of starttime and endtime (with calculated daily total) and I
would like to count how many people were at work before 8:30 am, how many
8:30 - 1:30, and finally how many from 1:30 on. Can you suggest how I can do
that?

Thanks in advance,
Bonnie

 

[Guest]

Hi Ofer,

Ignore my last question, figured that one out but I have another. I have
this code to figure the day of week WeekDayName(WeekDay([DateofWork])) and I
would like to have this report of time frames that you helped me with (which
works great thanks) give me the time frame for each day of week. So we would
see:

MONDAY
< 8:30

8:30 <1:30
1:30

ETC.

Just like you gave me but with the day of week breakdown. Can you help me
with this. When it put it in the WHERE criteria it gives me error messages.

Thanks in advance.

Bonnie

Try this SQL

SELECT IIf([StartTime]<#12/30/1899 8:30:0#,"Before
8:30",IIf([StartTime]>#12/30/1899 13:30:0#,"After 1:30","Between 8:30 and
1:30")) AS TimeFrame, Count(MyTable.StartTime) AS CountמתוךStartTime
FROM MyTable
GROUP BY IIf([StartTime]<#12/30/1899 8:30:0#,"Before
8:30",IIf([StartTime]>#12/30/1899 13:30:0#,"After 1:30","Between 8:30 and
1:30"))

Just Add a where statement

Hi Folks,

I have a table of starttime and endtime (with calculated daily total) and I
would like to count how many people were at work before 8:30 am, how many
8:30 - 1:30, and finally how many from 1:30 on. Can you suggest how I can do
that?

Thanks in advance,
Bonnie

 

[Guest]

To the query you created add WeekDayName(WeekDay([DateofWork])) as WeekOfDay
to the select
And to the group by add WeekDayName(WeekDay([DateofWork]))

That will splits the query to dates
Now If you want to create a report follow this steps
1. open a report in design view
2. Add the query to the record source of the report
3. Open the grouping and sorting dialog box in the view tool bar, and choose
the field WeekOfDay, and for this field ask to have a header, and ask it to
be sorted.
5. put the field WeekOfDay in the header
4. put the rest of the fields in the detail section

I hope that help
I will be available next in about 28 houres, if you have any more questions

Hi Ofer,

Ignore my last question, figured that one out but I have another. I have
this code to figure the day of week WeekDayName(WeekDay([DateofWork])) and I
would like to have this report of time frames that you helped me with (which
works great thanks) give me the time frame for each day of week. So we would
see:

MONDAY
< 8:30

8:30 <1:30
1:30

ETC.

Just like you gave me but with the day of week breakdown. Can you help me
with this. When it put it in the WHERE criteria it gives me error messages.

Thanks in advance.

Bonnie

Try this SQL

SELECT IIf([StartTime]<#12/30/1899 8:30:0#,"Before
8:30",IIf([StartTime]>#12/30/1899 13:30:0#,"After 1:30","Between 8:30 and
1:30")) AS TimeFrame, Count(MyTable.StartTime) AS CountמתוךStartTime
FROM MyTable
GROUP BY IIf([StartTime]<#12/30/1899 8:30:0#,"Before
8:30",IIf([StartTime]>#12/30/1899 13:30:0#,"After 1:30","Between 8:30 and
1:30"))

Just Add a where statement

Hi Folks,

I have a table of starttime and endtime (with calculated daily total) and I
would like to count how many people were at work before 8:30 am, how many
8:30 - 1:30, and finally how many from 1:30 on. Can you suggest how I can do
that?

Thanks in advance,
Bonnie

 

[Guest]

Hi Ofer,

You are really good with the date/time SQL stuff. It works great but for
one thing - the days of week are showing all randomly on the report. I tried
sorting ASC or DESC and it doesn't fix it. I think it might be sorting on
WeekDay instead of WeekDayName (tell me if I'm wrong) but I don't know how to
fix it.

Your help is appreciated. Look forward to getting your opinion.

Thanks.

To the query you created add WeekDayName(WeekDay([DateofWork])) as WeekOfDay
to the select
And to the group by add WeekDayName(WeekDay([DateofWork]))

That will splits the query to dates
Now If you want to create a report follow this steps
1. open a report in design view
2. Add the query to the record source of the report
3. Open the grouping and sorting dialog box in the view tool bar, and choose
the field WeekOfDay, and for this field ask to have a header, and ask it to
be sorted.
5. put the field WeekOfDay in the header
4. put the rest of the fields in the detail section

I hope that help
I will be available next in about 28 houres, if you have any more questions

Hi Ofer,

Ignore my last question, figured that one out but I have another. I have
this code to figure the day of week WeekDayName(WeekDay([DateofWork])) and I
would like to have this report of time frames that you helped me with (which
works great thanks) give me the time frame for each day of week. So we would
see:

MONDAY
< 8:30

8:30 <1:30
1:30

ETC.

Just like you gave me but with the day of week breakdown. Can you help me
with this. When it put it in the WHERE criteria it gives me error messages.

Thanks in advance.

Bonnie

Try this SQL

SELECT IIf([StartTime]<#12/30/1899 8:30:0#,"Before
8:30",IIf([StartTime]>#12/30/1899 13:30:0#,"After 1:30","Between 8:30 and
1:30")) AS TimeFrame, Count(MyTable.StartTime) AS CountמתוךStartTime
FROM MyTable
GROUP BY IIf([StartTime]<#12/30/1899 8:30:0#,"Before
8:30",IIf([StartTime]>#12/30/1899 13:30:0#,"After 1:30","Between 8:30 and
1:30"))

Just Add a where statement


:

Hi Folks,

I have a table of starttime and endtime (with calculated daily total) and I
would like to count how many people were at work before 8:30 am, how many
8:30 - 1:30, and finally how many from 1:30 on. Can you suggest how I can do
that?

Thanks in advance,
Bonnie

 

[Steve Schapel]

Bonnie,

That's what I thought. So now I'm confused. You have said elsewhere in
this thread that the concept you have been working with "works great",
whereas this only relates to starting times, and I would expect it to
give incorrect results. To get an accurate readout of who is "at work",
you will need a quite different concept. For the record, this is how I
would have approached it...

SELECT YourTable.DateOfWork, Format([DateOfWork],"dddd") AS DayOfWork,
-Sum([StartTime]<#08:30#) AS [Before 0830], -Sum(([StartTime] Between
#08:30# And #13:30#) Or ([EndTime] Between #08:30# And #13:30#) Or
([StartTime]<#08:30# And [EndTime]>#13:30#)) AS [0830 to 1330],
-Sum([EndTime]>#13:30#) AS [After 1330]
FROM YourTable
GROUP BY YourTable.DateOfWork, Format([DateOfWork],"dddd"),
Weekday([DateOfWork])
ORDER BY Weekday([DateOfWork])

 

[Guest]

What Ofer suggested is working pretty good except that my day of week is not
sorted in order. But I'm going to try your suggesting and make sure
something isn't missing the calculation. Question, you SQL codes says
DateofWork - I only have times in the fields, not dates. Will this work?

Bonnie,

That's what I thought. So now I'm confused. You have said elsewhere in
this thread that the concept you have been working with "works great",
whereas this only relates to starting times, and I would expect it to
give incorrect results. To get an accurate readout of who is "at work",
you will need a quite different concept. For the record, this is how I
would have approached it...

SELECT YourTable.DateOfWork, Format([DateOfWork],"dddd") AS DayOfWork,
-Sum([StartTime]<#08:30#) AS [Before 0830], -Sum(([StartTime] Between
#08:30# And #13:30#) Or ([EndTime] Between #08:30# And #13:30#) Or
([StartTime]<#08:30# And [EndTime]>#13:30#)) AS [0830 to 1330],
-Sum([EndTime]>#13:30#) AS [After 1330]
FROM YourTable
GROUP BY YourTable.DateOfWork, Format([DateOfWork],"dddd"),
Weekday([DateOfWork])
ORDER BY Weekday([DateOfWork])

--
Steve Schapel, Microsoft Access MVP

Hi Steve,

I don't think I have quite figured out what being "at work" means yet.
Sometimes it means someone is holding up a wall or keeping a seat warm. But
for my programming purposes here it means that they clocked in at one time
and clocked out by another so if they hadn't clocked out yet we will consider
them at work.

Thanks.

 

[Guest]

Sorry, you were right, I did have day of week and the code works like a
dream. Thanks so much for the help ya'll.

What Ofer suggested is working pretty good except that my day of week is not
sorted in order. But I'm going to try your suggesting and make sure
something isn't missing the calculation. Question, you SQL codes says
DateofWork - I only have times in the fields, not dates. Will this work?

Bonnie,

That's what I thought. So now I'm confused. You have said elsewhere in
this thread that the concept you have been working with "works great",
whereas this only relates to starting times, and I would expect it to
give incorrect results. To get an accurate readout of who is "at work",
you will need a quite different concept. For the record, this is how I
would have approached it...

SELECT YourTable.DateOfWork, Format([DateOfWork],"dddd") AS DayOfWork,
-Sum([StartTime]<#08:30#) AS [Before 0830], -Sum(([StartTime] Between
#08:30# And #13:30#) Or ([EndTime] Between #08:30# And #13:30#) Or
([StartTime]<#08:30# And [EndTime]>#13:30#)) AS [0830 to 1330],
-Sum([EndTime]>#13:30#) AS [After 1330]
FROM YourTable
GROUP BY YourTable.DateOfWork, Format([DateOfWork],"dddd"),
Weekday([DateOfWork])
ORDER BY Weekday([DateOfWork])

--
Steve Schapel, Microsoft Access MVP

Hi Steve,

I don't think I have quite figured out what being "at work" means yet.
Sometimes it means someone is holding up a wall or keeping a seat warm. But
for my programming purposes here it means that they clocked in at one time
and clocked out by another so if they hadn't clocked out yet we will consider
them at work.

Thanks.

 

[Steve Schapel]

Bonnie,

What Ofer suggested is working pretty good ...

If I understand the situation correctly, if Charlie clocks in at 8:00am
and works until 3:00pm, he should be counted as 1 person for each of the
3 categories: Before 8:30, 8:30-1:30, and After 1:30, since he is "at
work" during each of the 3 periods. Is this right? With all due
respect to Ofer, I think his suggested solution will only count Charlie
once in the Before 8:30 category, which (as far as I can see) is an
incorrect result.

 

[Guest]

I see that now. You're correct. Thanks for seeing that. We are building a
large convention center and we have many volunteers and I am capturing how
many volunteers are there for meals so we can predict food need for the food
department guys.

Thanks very much.

 

[Steve Schapel]

Bonnie,

... so we can predict food need

Hmmm. Mission-critical application, eh?

 

[Guest]

Can't think of anything more mission-critical.

Bonnie,


Hmmm. Mission-critical application, eh?