Quarterly Overtime Hours Reply

R

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.
 
J

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]
 
R

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

Rudy said:
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.
Click to expand...


.
Click to expand...
 
R

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

Rudy said:
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
Click to expand...
Click to expand...
to
my


.
Click to expand...
.
Click to expand...
 
J

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

Rudy said:
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
Click to expand...
to
my
table called Time Sheet.


.
Click to expand...
.
Click to expand...
Click to expand...
 
R

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

Rudy said:
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
Click to expand...
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.


.

.
Click to expand...
Click to expand...


.
Click to expand...
 
J

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

Rudy said:
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

Rudy said:
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.


.

.
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Quarterly Overtime Hours 1
Duplicate Field Names 6
Update Query 2
Negative Numbers 2
Change expression from month to quarter 7
Group by date on employeeID 4
QUERY: Quarterly Subtotals on Multi-Year Report 3
Overtime DB Help Needed 13

Top