add selected records

[Brian via AccessMonster.com]

Hello. Thank you in advance for your help! I have a table with fields for
employee ID, WeekEndingDate, and HoursWorked. I would like to create a query
to total HoursWorked, with WeekEndingDate as a parameter. For example, I may
want to look at the data monthly sometimes, other times quarterly. I've tried
DSum, but get a #Error message. Any help appreciated! I'm new to this.

 

[OfficeDev18 via AccessMonster.com]

Hello, Brian.

Are your WeekEndingDate and HoursWorked really date and numeric fields
respectively, or are they string fields which need to be converted before
calculating?

Sam

 

[Brian via AccessMonster.com]

Hi Sam
Yes to both. WeekEndingDate is Date/Time, HoursWorked is Number.

 

[OfficeDev18 via AccessMonster.com]

Hi,

OK, let's try this: SELECT [employee ID], Sum(HoursWorked) As
SumOfHoursWorked FROM YourTableName WHERE WeekEndingDate = CDate([Enter a
Week Ending Date]) GROUP BY [employee ID];

Copy and paste the SQL into Access's query designer, and replace
YourTableName and [employee ID] field with the correct names. When you run
the query, you will be prompted to "Enter a Week Ending Date". That will give
you your weekly query.

Try this first, and then we'll try a quarterly query, which is more involved.

Sam

Hi Sam
Yes to both. WeekEndingDate is Date/Time, HoursWorked is Number.

Hello, Brian.

[quoted text clipped - 9 lines]

 

[Brian via AccessMonster.com]

Thanks Sam. I constructed the "Weekly " query as you described. How to do
monthly or quarterly (which would need to add several records)? Thank you for
your time!!!!!!!!

Hi,

OK, let's try this: SELECT [employee ID], Sum(HoursWorked) As
SumOfHoursWorked FROM YourTableName WHERE WeekEndingDate = CDate([Enter a
Week Ending Date]) GROUP BY [employee ID];

Copy and paste the SQL into Access's query designer, and replace
YourTableName and [employee ID] field with the correct names. When you run
the query, you will be prompted to "Enter a Week Ending Date". That will give
you your weekly query.

Try this first, and then we'll try a quarterly query, which is more involved.

Sam

Hi Sam
Yes to both. WeekEndingDate is Date/Time, HoursWorked is Number.

[quoted text clipped - 4 lines]

 

[OfficeDev18 via AccessMonster.com]

Hi, Brian,

and I'm glad that worked. I will only be able to do part of the job right now,
because I'm badly pressed for time. I hope you don't mind.

OK, for the monthly, let's try this: SELECT [employee ID], Sum(HoursWorked)
As
SumOfHoursWorked FROM YourTableName WHERE (Month(WeekEndingDate) = CInt(
[Enter a
Month Number])) And (Year(WeekEndingDate) = CInt([Enter a Year])) GROUP BY
[employee ID];

This will prompt you for both "Enter a Month Number" and "Enter a Year", to
accommodate reporting in January for December of last year.

By the way, I'm still in the dark regarding the actual table and field names.
It might be less of a guess if I knew these names.

Thanks,

Sam

Thanks Sam. I constructed the "Weekly " query as you described. How to do
monthly or quarterly (which would need to add several records)? Thank you for
your time!!!!!!!!

Hi,

[quoted text clipped - 16 lines]

 

[Brian via AccessMonster.com]

Hi Sam. I'm very thankful for your help; no worries about only doing part of
it.

The last suggestion worked well (no surprise to you, I'm sure, but, being new,
I continue to be amazed when something in Access that I work on actually
works!); I made a second version (modified) so as not to be prompted for
employee ID. My thought is that I'll want to make a report based on this
query which will list all employees and their units/hour for productivity.
So, here's what I have:

SELECT tblHoursWorked.EmployeeID, Sum(tblHoursWorked.HoursWorked) AS
SumOfHoursWorked
FROM tblHoursWorked
WHERE (((Month([WeekEndingDate]))=CInt([Enter a Month Number])) AND ((Year(
[WeekEndingDate]))=Int([Enter a Year])))
GROUP BY tblHoursWorked.EmployeeID;

As you can see, the table name is tblHoursWorked; EmployeeID is their unique
identifier. Again, I really appreciate your help.

Brian

Hi, Brian,

and I'm glad that worked. I will only be able to do part of the job right now,
because I'm badly pressed for time. I hope you don't mind.

OK, for the monthly, let's try this: SELECT [employee ID], Sum(HoursWorked)
As
SumOfHoursWorked FROM YourTableName WHERE (Month(WeekEndingDate) = CInt(
[Enter a
Month Number])) And (Year(WeekEndingDate) = CInt([Enter a Year])) GROUP BY
[employee ID];

This will prompt you for both "Enter a Month Number" and "Enter a Year", to
accommodate reporting in January for December of last year.

By the way, I'm still in the dark regarding the actual table and field names.
It might be less of a guess if I knew these names.

Thanks,

Sam

Thanks Sam. I constructed the "Weekly " query as you described. How to do
monthly or quarterly (which would need to add several records)? Thank you for

[quoted text clipped - 5 lines]

 

[OfficeDev18 via AccessMonster.com]

Now for the coup de grace, the quarterly query:

SELECT tblHoursWorked.EmployeeID, Sum(tblHoursWorked.HoursWorked) AS
SumOfHoursWorked
FROM tblHoursWorked
WHERE [WeekEndingDate] Between DateSerial([Enter Year],3*CInt([Enter Quarter
Number])-2,1) And (DateSerial([Enter Year],3*CInt([Enter Quarter Number]),1)-
1)
GROUP BY tblHoursWorked.EmployeeID;

This will prompt you to "Enter Year" and "Enter Quarter Number", which will
expect the response 1 through 4.

By the way, this may not worked 100% as expected, because week-end dates
being what they are, the week BEGINNING, of course, may be in the previous
quarter. If the week-ending date is 1/1/06, the week beginning was 12/26/05.
You may want to make an adjustment for this, but it's probably not mandatory.

Good luck,

Sam

Hi Sam. I'm very thankful for your help; no worries about only doing part of
it.

The last suggestion worked well (no surprise to you, I'm sure, but, being new,
I continue to be amazed when something in Access that I work on actually
works!); I made a second version (modified) so as not to be prompted for
employee ID. My thought is that I'll want to make a report based on this
query which will list all employees and their units/hour for productivity.
So, here's what I have:

SELECT tblHoursWorked.EmployeeID, Sum(tblHoursWorked.HoursWorked) AS
SumOfHoursWorked
FROM tblHoursWorked
WHERE (((Month([WeekEndingDate]))=CInt([Enter a Month Number])) AND ((Year(
[WeekEndingDate]))=Int([Enter a Year])))
GROUP BY tblHoursWorked.EmployeeID;

As you can see, the table name is tblHoursWorked; EmployeeID is their unique
identifier. Again, I really appreciate your help.

Brian

Hi, Brian,

[quoted text clipped - 23 lines]

 

[OfficeDev18 via AccessMonster.com]

Oops, I goofed. Change that to:

SELECT tblHoursWorked.EmployeeID, Sum(tblHoursWorked.HoursWorked) AS
SumOfHoursWorked
FROM tblHoursWorked
WHERE [WeekEndingDate] Between DateSerial([Enter Year],3*CInt([Enter Quarter
Number])-2,1) And (DateAdd("q",1,DateSerial([Enter Year],3*CInt([Enter
Quarter Number])-2,1))-
1)
GROUP BY tblHoursWorked.EmployeeID;

If you do it the previous wway, it won't include the last month of the
quarter.

Sam

Now for the coup de grace, the quarterly query:

SELECT tblHoursWorked.EmployeeID, Sum(tblHoursWorked.HoursWorked) AS
SumOfHoursWorked
FROM tblHoursWorked
WHERE [WeekEndingDate] Between DateSerial([Enter Year],3*CInt([Enter Quarter
Number])-2,1) And (DateSerial([Enter Year],3*CInt([Enter Quarter Number]),1)-
1)
GROUP BY tblHoursWorked.EmployeeID;

This will prompt you to "Enter Year" and "Enter Quarter Number", which will
expect the response 1 through 4.

By the way, this may not worked 100% as expected, because week-end dates
being what they are, the week BEGINNING, of course, may be in the previous
quarter. If the week-ending date is 1/1/06, the week beginning was 12/26/05.
You may want to make an adjustment for this, but it's probably not mandatory.

Good luck,

Sam

Hi Sam. I'm very thankful for your help; no worries about only doing part of
it.

[quoted text clipped - 23 lines]

 

[Brian via AccessMonster.com]

Works splendidly, of course! Thanks for working this out!! I appreciate you
fitting it into a busy schedule. One additional question: Will I be able to
have the same query sum records from a different table for the same date
range by placing the field name in a column in any of those queries (week,
month, quarter)?

Thanks Sam.
Brian

Now for the coup de grace, the quarterly query:

SELECT tblHoursWorked.EmployeeID, Sum(tblHoursWorked.HoursWorked) AS
SumOfHoursWorked
FROM tblHoursWorked
WHERE [WeekEndingDate] Between DateSerial([Enter Year],3*CInt([Enter Quarter
Number])-2,1) And (DateSerial([Enter Year],3*CInt([Enter Quarter Number]),1)-
1)
GROUP BY tblHoursWorked.EmployeeID;

This will prompt you to "Enter Year" and "Enter Quarter Number", which will
expect the response 1 through 4.

By the way, this may not worked 100% as expected, because week-end dates
being what they are, the week BEGINNING, of course, may be in the previous
quarter. If the week-ending date is 1/1/06, the week beginning was 12/26/05.
You may want to make an adjustment for this, but it's probably not mandatory.

Good luck,

Sam

Hi Sam. I'm very thankful for your help; no worries about only doing part of
it.

[quoted text clipped - 23 lines]

 

[OfficeDev18 via AccessMonster.com]

Great, Brian, I'm glad it worked out for you.

To answer your question, the only way it would work is if you join it to
tblHoursWorked and change your FROM clause appropriately.

It COULD be made to work. However, I would simply copy the SQL statement and
modify it for the other table. It's less messy.

Sam

Works splendidly, of course! Thanks for working this out!! I appreciate you
fitting it into a busy schedule. One additional question: Will I be able to
have the same query sum records from a different table for the same date
range by placing the field name in a column in any of those queries (week,
month, quarter)?

Thanks Sam.
Brian

Now for the coup de grace, the quarterly query:

[quoted text clipped - 23 lines]