Quarterly Overtime Hours Reply

[Rudy]

I have several queries to keep track of hours and
overtime hours. However, I'd like a query that can track
overtime hours by the quarter. So that at the end of a
quarter I can track this value. I've been using the

IIf function to try. This is what I've tried.

OTHours: IIf([Sum Of Hours Worked]>'q yyyy',[Sum Of Hours
Worked]-'q yyyy',0)

Can you suggest an alternative that works


The returned suggestion was:

SELECT Format([WorkDate],"q yyyy") AS TheQtr, Sum
(HoursWorked) AS
SumOfHoursWorked
FROM YourTable
GROUP BY Format([WorkDate],"q yyyy");

However, I'm really not sure where/How to use this reply.
I entered the above in a new query using the SQL view.
Once entered it failed when I changed the YourTable to my
table called Time Sheet.

 

[Joan Wild]

Hi Rudy

It would help if you replied in the original thread.

You would need to substitute the appropriate names of your fields and tables
in the sample SQL.

You mention doing this for the table name, however also ensure the field
names are correct.

Also if any of your names include spaces, enclose the name in square
brackets.

[Time Sheet]

 

[Rudy]

Joan:
Sorry about that. I've done so in teh past and rec'd no
reply. I'll try your suggestions and reply to this
message.

Thanks

-----Original Message-----
Hi Rudy

It would help if you replied in the original thread.

You would need to substitute the appropriate names of your fields and tables
in the sample SQL.

You mention doing this for the table name, however also ensure the field
names are correct.

Also if any of your names include spaces, enclose the name in square
brackets.

[Time Sheet]

--
Joan Wild
Microsoft Access MVP

I have several queries to keep track of hours and
overtime hours. However, I'd like a query that can track
overtime hours by the quarter. So that at the end of a
quarter I can track this value. I've been using the

IIf function to try. This is what I've tried.

OTHours: IIf([Sum Of Hours Worked]>'q yyyy',[Sum Of Hours
Worked]-'q yyyy',0)

Can you suggest an alternative that works


The returned suggestion was:

SELECT Format([WorkDate],"q yyyy") AS TheQtr, Sum
(HoursWorked) AS
SumOfHoursWorked
FROM YourTable
GROUP BY Format([WorkDate],"q yyyy");

However, I'm really not sure where/How to use this reply.
I entered the above in a new query using the SQL view.
Once entered it failed when I changed the YourTable to my
table called Time Sheet.

.

 

[Rudy]

Joan:

With the mod as suggested I created:
SELECT Format([Date],"""Quart""q yyyy") AS TheQtr, Sum
[Hours Query].[Hours Worked]) AS SumOfHoursWorked
FROM [Hours Query]
GROUP BY Format([Date],"""Quart""q yyyy");

Which gives me the sum of hours worked in a quart. What
I'd like now is to generate the sum of quarterly overtime
hours based on a 7.5hr day. Hope this is possible.

Rudy

-----Original Message-----
Joan:
Sorry about that. I've done so in teh past and rec'd no
reply. I'll try your suggestions and reply to this
message.

Thanks

-----Original Message-----
Hi Rudy

It would help if you replied in the original thread.

You would need to substitute the appropriate names of your fields and tables
in the sample SQL.

You mention doing this for the table name, however also ensure the field
names are correct.

Also if any of your names include spaces, enclose the name in square
brackets.

[Time Sheet]

--
Joan Wild
Microsoft Access MVP

I have several queries to keep track of hours and
overtime hours. However, I'd like a query that can track
overtime hours by the quarter. So that at the end of a
quarter I can track this value. I've been using the

IIf function to try. This is what I've tried.

OTHours: IIf([Sum Of Hours Worked]>'q yyyy',[Sum Of Hours
Worked]-'q yyyy',0)

Can you suggest an alternative that works


The returned suggestion was:

SELECT Format([WorkDate],"q yyyy") AS TheQtr, Sum
(HoursWorked) AS
SumOfHoursWorked
FROM YourTable
GROUP BY Format([WorkDate],"q yyyy");

However, I'm really not sure where/How to use this reply.
I entered the above in a new query using the SQL view.
Once entered it failed when I changed the YourTable

to

my


.

.

 

[Joan Wild]

I would think you could get this in the Hours Query. I noticed your
original SQL reference a table called Time Sheet.

What is the purpose of the Hours Query, and what is its SQL statement?

--
Joan Wild
Microsoft Access MVP

Joan:

With the mod as suggested I created:
SELECT Format([Date],"""Quart""q yyyy") AS TheQtr, Sum
[Hours Query].[Hours Worked]) AS SumOfHoursWorked
FROM [Hours Query]
GROUP BY Format([Date],"""Quart""q yyyy");

Which gives me the sum of hours worked in a quart. What
I'd like now is to generate the sum of quarterly overtime
hours based on a 7.5hr day. Hope this is possible.

Rudy

-----Original Message-----
Joan:
Sorry about that. I've done so in teh past and rec'd no
reply. I'll try your suggestions and reply to this
message.

Thanks

-----Original Message-----
Hi Rudy

It would help if you replied in the original thread.

You would need to substitute the appropriate names of your fields and tables
in the sample SQL.

You mention doing this for the table name, however also ensure the field
names are correct.

Also if any of your names include spaces, enclose the name in square
brackets.

[Time Sheet]

--
Joan Wild
Microsoft Access MVP

I have several queries to keep track of hours and
overtime hours. However, I'd like a query that can track
overtime hours by the quarter. So that at the end of a
quarter I can track this value. I've been using the

IIf function to try. This is what I've tried.

OTHours: IIf([Sum Of Hours Worked]>'q yyyy',[Sum Of Hours
Worked]-'q yyyy',0)

Can you suggest an alternative that works


The returned suggestion was:

SELECT Format([WorkDate],"q yyyy") AS TheQtr, Sum
(HoursWorked) AS
SumOfHoursWorked
FROM YourTable
GROUP BY Format([WorkDate],"q yyyy");

However, I'm really not sure where/How to use this reply.
I entered the above in a new query using the SQL view.
Once entered it failed when I changed the YourTable

to

my
table called Time Sheet.


.

.

 

[Rudy]

The hours Query is where I am calcualting the hours
worked, start & end times and from there the Overtime.
The SQL statement is:

SELECT [Time Sheet].PrimeKey, [Time Sheet].Date, [Time
Sheet].StartTimeAM, [Time Sheet].EndTimeAM, [Time
Sheet].StartTimePM, [Time Sheet].EndTimePM, (([EndTimeAM]-
[StartTimeAM])+([EndTimePM]-[StartTimePM]))*24 AS [Hours
Worked], IIf([Hours Worked]>7.5,[Hours Worked]-7.5,0) AS
OTHours, IIf([Hours Worked]>7.5,[Date]) AS OTHoursDate,
[Time Sheet].RecoveredOTHrs, [Time
Sheet].RecoveredOTHrsDate
FROM [Time Sheet]
GROUP BY [Time Sheet].PrimeKey, [Time Sheet].Date, [Time
Sheet].StartTimeAM, [Time Sheet].EndTimeAM, [Time
Sheet].StartTimePM, [Time Sheet].EndTimePM, IIf([Hours
Worked]>7.5,[Date]), [Time Sheet].RecoveredOTHrs, [Time
Sheet].RecoveredOTHrsDate;

-----Original Message-----
I would think you could get this in the Hours Query. I noticed your
original SQL reference a table called Time Sheet.

What is the purpose of the Hours Query, and what is its SQL statement?

--
Joan Wild
Microsoft Access MVP

Joan:

With the mod as suggested I created:
SELECT Format([Date],"""Quart""q yyyy") AS TheQtr, Sum
[Hours Query].[Hours Worked]) AS SumOfHoursWorked
FROM [Hours Query]
GROUP BY Format([Date],"""Quart""q yyyy");

Which gives me the sum of hours worked in a quart. What
I'd like now is to generate the sum of quarterly overtime
hours based on a 7.5hr day. Hope this is possible.

Rudy

-----Original Message-----
Joan:
Sorry about that. I've done so in teh past and rec'd no
reply. I'll try your suggestions and reply to this
message.

Thanks
-----Original Message-----
Hi Rudy

It would help if you replied in the original thread.

You would need to substitute the appropriate names of
your fields and tables
in the sample SQL.

You mention doing this for the table name, however also
ensure the field
names are correct.

Also if any of your names include spaces, enclose the
name in square
brackets.

[Time Sheet]

--
Joan Wild
Microsoft Access MVP

I have several queries to keep track of hours and
overtime hours. However, I'd like a query that can
track
overtime hours by the quarter. So that at the end

of
a

quarter I can track this value. I've been using the

IIf function to try. This is what I've tried.

OTHours: IIf([Sum Of Hours Worked]>'q yyyy',[Sum Of
Hours
Worked]-'q yyyy',0)

Can you suggest an alternative that works


The returned suggestion was:

SELECT Format([WorkDate],"q yyyy") AS TheQtr, Sum
(HoursWorked) AS
SumOfHoursWorked
FROM YourTable
GROUP BY Format([WorkDate],"q yyyy");

However, I'm really not sure where/How to use this
reply.
I entered the above in a new query using the SQL view.
Once entered it failed when I changed the YourTable to
my
table called Time Sheet.


.

.

.

 

[Joan Wild]

Then change your quarterly query to

SELECT Format([OTHoursDate],"""Quart""q yyyy") AS TheQtr, Sum
[Hours Query].[OTHours]) AS SumOfOTHours
FROM [Hours Query]
GROUP BY Format([OTHoursDate],"""Quart""q yyyy");

--
Joan Wild
Microsoft Access MVP

The hours Query is where I am calcualting the hours
worked, start & end times and from there the Overtime.
The SQL statement is:

SELECT [Time Sheet].PrimeKey, [Time Sheet].Date, [Time
Sheet].StartTimeAM, [Time Sheet].EndTimeAM, [Time
Sheet].StartTimePM, [Time Sheet].EndTimePM, (([EndTimeAM]-
[StartTimeAM])+([EndTimePM]-[StartTimePM]))*24 AS [Hours
Worked], IIf([Hours Worked]>7.5,[Hours Worked]-7.5,0) AS
OTHours, IIf([Hours Worked]>7.5,[Date]) AS OTHoursDate,
[Time Sheet].RecoveredOTHrs, [Time
Sheet].RecoveredOTHrsDate
FROM [Time Sheet]
GROUP BY [Time Sheet].PrimeKey, [Time Sheet].Date, [Time
Sheet].StartTimeAM, [Time Sheet].EndTimeAM, [Time
Sheet].StartTimePM, [Time Sheet].EndTimePM, IIf([Hours
Worked]>7.5,[Date]), [Time Sheet].RecoveredOTHrs, [Time
Sheet].RecoveredOTHrsDate;

-----Original Message-----
I would think you could get this in the Hours Query. I noticed your
original SQL reference a table called Time Sheet.

What is the purpose of the Hours Query, and what is its SQL statement?

--
Joan Wild
Microsoft Access MVP

Joan:

With the mod as suggested I created:
SELECT Format([Date],"""Quart""q yyyy") AS TheQtr, Sum
[Hours Query].[Hours Worked]) AS SumOfHoursWorked
FROM [Hours Query]
GROUP BY Format([Date],"""Quart""q yyyy");

Which gives me the sum of hours worked in a quart. What
I'd like now is to generate the sum of quarterly overtime
hours based on a 7.5hr day. Hope this is possible.

Rudy

-----Original Message-----
Joan:
Sorry about that. I've done so in teh past and rec'd no
reply. I'll try your suggestions and reply to this
message.

Thanks
-----Original Message-----
Hi Rudy

It would help if you replied in the original thread.

You would need to substitute the appropriate names of
your fields and tables
in the sample SQL.

You mention doing this for the table name, however also
ensure the field
names are correct.

Also if any of your names include spaces, enclose the
name in square
brackets.

[Time Sheet]

--
Joan Wild
Microsoft Access MVP

I have several queries to keep track of hours and
overtime hours. However, I'd like a query that can
track
overtime hours by the quarter. So that at the end of
a
quarter I can track this value. I've been using the

IIf function to try. This is what I've tried.

OTHours: IIf([Sum Of Hours Worked]>'q yyyy',[Sum Of
Hours
Worked]-'q yyyy',0)

Can you suggest an alternative that works


The returned suggestion was:

SELECT Format([WorkDate],"q yyyy") AS TheQtr, Sum
(HoursWorked) AS
SumOfHoursWorked
FROM YourTable
GROUP BY Format([WorkDate],"q yyyy");

However, I'm really not sure where/How to use this
reply.
I entered the above in a new query using the SQL view.
Once entered it failed when I changed the YourTable
to
my
table called Time Sheet.


.

.

.