Query proper results..?

Jorge · Nov 26, 2004

Hello - Happy Holiday's..

I have a Table(s) created - that downloads all employee information so I can
calculate hours and reports etc.
Everything seems to work fine. However I have a new calculation that I need
to produce results.

The data would eventually represent a continuing/rolling 52 weeks. The
queries would be at various holiday times and measure REGULAR ( up to the
first 40 worked per week ), to sum the number of regular hours worked in the
immediate past 13 weeks.

ex table..

| Fname | Lname | Emp# | Work_Week | Type | Bill_Hrs | Pay_Hours |
Mike Smith 12345 10/31/2004 REG 40 40
Mike Smith 12345 10/31/2004 OT 6 6

Can anyone offer any help on this issue?
It may seem like any easy query but I cannot get the proper answer...

Thank-you
Jorge

Steve Schapel · Nov 26, 2004

Jorge,

User the query design view to make a query based on your table, and add
the Pay_Hours, Type, and Work_Week fields to the query design grid.
Make it a Totals Query (select Totals from the View menu). In the
Totals row of thegrid for the Pay_Hours field, put Sum, and in the
Totals row of the Type field put Where and in the Criteria row put
"REG", and in the Totals row of the Work_Week field put Where and in the
Criteria row put >DateAdd("ww",-13,Date())

The SQL view of this query should look something like...
SELECT Sum([Pay_Hours]) As TotalHours
FROM [ex table]
WHERE ([Type]="REG") And ([Work_Week]>DateAdd("ww",-13,Date()))

By the way, as an aside, it is not a good idea to use a # as part of a
field name.

John Vinson · Nov 26, 2004

The data would eventually represent a continuing/rolling 52 weeks. The
queries would be at various holiday times and measure REGULAR ( up to the
first 40 worked per week ), to sum the number of regular hours worked in the
immediate past 13 weeks.

ex table..

| Fname | Lname | Emp# | Work_Week | Type | Bill_Hrs | Pay_Hours |
Mike Smith 12345 10/31/2004 REG 40 40
Mike Smith 12345 10/31/2004 OT 6 6

Can anyone offer any help on this issue?
It may seem like any easy query but I cannot get the proper answer...

Hrm. How about:

SELECT First([FName]), First([LName]), [Emp#], Sum([Pay_Hours])
FROM tablename
WHERE [Type] = "REG"
AND [Work_Week] BETWEEN DateAdd("ww", -13, Date()) AND Date()
GROUP BY [Emp#];

Or post your SQL and indicate what "improper" results you're getting.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

Jorge · Nov 29, 2004

Ok I had to modify it a little - but still not getting the results I'd like.
I know in the example I put only one table - but there is actually 2.
Employee & Adecco. Adecco contains all the details of each employee.

What I get is each employee multiple times -for each week. I would like to
get a sum of each employee fitting the criteria.

My SqL code looks as such.
SELECT Employee.emp_id, adecco.first_name, adecco.last_name, adecco.trc,
adecco.week_worked_date, Sum(adecco.pay_hours_adjusted) AS TotalHours
FROM Employee INNER JOIN adecco ON Employee.emp_id = adecco.emp_id
WHERE (((adecco.trc)="REG") AND
((adecco.week_worked_date)>DateAdd("ww",-13,Date())))
GROUP BY Employee.emp_id, adecco.first_name, adecco.last_name, adecco.trc,
adecco.week_worked_date
ORDER BY adecco.week_worked_date DESC;

Answer:
Emp# Firstname Lastname TRC Week Worked TotalHours
107 SHAWN WELCH REG 9/5/2004 40
107 SHAWN WELCH REG 9/12/2004 40
107 SHAWN WELCH REG 9/19/2004 120
107 SHAWN WELCH REG 9/26/2004 40
107 SHAWN WELCH REG 10/3/2004 40
107 SHAWN WELCH REG 10/31/2004 40
107 SHAWN WELCH REG 11/7/2004 40

help?

John Vinson said:
The data would eventually represent a continuing/rolling 52 weeks. The
queries would be at various holiday times and measure REGULAR ( up to the
first 40 worked per week ), to sum the number of regular hours worked in
the
immediate past 13 weeks.

ex table..

| Fname | Lname | Emp# | Work_Week | Type | Bill_Hrs | Pay_Hours |
Mike Smith 12345 10/31/2004 REG 40 40
Mike Smith 12345 10/31/2004 OT 6 6

Can anyone offer any help on this issue?
It may seem like any easy query but I cannot get the proper answer...

Click to expand...

Hrm. How about:

SELECT First([FName]), First([LName]), [Emp#], Sum([Pay_Hours])
FROM tablename
WHERE [Type] = "REG"
AND [Work_Week] BETWEEN DateAdd("ww", -13, Date()) AND Date()
GROUP BY [Emp#];

Or post your SQL and indicate what "improper" results you're getting.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

Dave · Nov 29, 2004

I think you should get the results you want if you just remove the
week_worked column

SELECT Employee.emp_id, adecco.first_name, adecco.last_name, adecco.trc,
Sum(adecco.pay_hours_adjusted) AS TotalHours
FROM Employee INNER JOIN adecco ON Employee.emp_id = adecco.emp_id
WHERE (((adecco.trc)="REG") AND
((adecco.week_worked_date)>DateAdd("ww",-13,Date())))
GROUP BY Employee.emp_id, adecco.first_name, adecco.last_name, adecco.trc;

Since you want the total hours (yes?) there's no need to sort by week or to
refer to the date except in the WHERE clause

Jorge said:
Ok I had to modify it a little - but still not getting the results I'd like.
I know in the example I put only one table - but there is actually 2.
Employee & Adecco. Adecco contains all the details of each employee.

What I get is each employee multiple times -for each week. I would like to
get a sum of each employee fitting the criteria.

My SqL code looks as such.
SELECT Employee.emp_id, adecco.first_name, adecco.last_name, adecco.trc,
adecco.week_worked_date, Sum(adecco.pay_hours_adjusted) AS TotalHours
FROM Employee INNER JOIN adecco ON Employee.emp_id = adecco.emp_id
WHERE (((adecco.trc)="REG") AND
((adecco.week_worked_date)>DateAdd("ww",-13,Date())))
GROUP BY Employee.emp_id, adecco.first_name, adecco.last_name, adecco.trc,
adecco.week_worked_date
ORDER BY adecco.week_worked_date DESC;

Answer:
Emp# Firstname Lastname TRC Week Worked TotalHours
107 SHAWN WELCH REG 9/5/2004 40
107 SHAWN WELCH REG 9/12/2004 40
107 SHAWN WELCH REG 9/19/2004 120
107 SHAWN WELCH REG 9/26/2004 40
107 SHAWN WELCH REG 10/3/2004 40
107 SHAWN WELCH REG 10/31/2004 40
107 SHAWN WELCH REG 11/7/2004 40

help?

John Vinson said:

The data would eventually represent a continuing/rolling 52 weeks. The
queries would be at various holiday times and measure REGULAR ( up to the
first 40 worked per week ), to sum the number of regular hours worked in
the
immediate past 13 weeks.

ex table..

| Fname | Lname | Emp# | Work_Week | Type | Bill_Hrs | Pay_Hours |
Mike Smith 12345 10/31/2004 REG 40 40
Mike Smith 12345 10/31/2004 OT 6 6

Can anyone offer any help on this issue?
It may seem like any easy query but I cannot get the proper answer...

Click to expand...

Hrm. How about:

SELECT First([FName]), First([LName]), [Emp#], Sum([Pay_Hours])
FROM tablename
WHERE [Type] = "REG"
AND [Work_Week] BETWEEN DateAdd("ww", -13, Date()) AND Date()
GROUP BY [Emp#];

Or post your SQL and indicate what "improper" results you're getting.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

Click to expand...

John Vinson · Nov 29, 2004

Ok I had to modify it a little - but still not getting the results I'd like.
I know in the example I put only one table - but there is actually 2.
Employee & Adecco. Adecco contains all the details of each employee.

What I get is each employee multiple times -for each week. I would like to
get a sum of each employee fitting the criteria.

Then remove the column where you're Grouping By and sorting by
week_worked_date. As it is, you're sorting by week_worked_date and
grouping by it, so you'll get the sum for each week. It's doing what
you're asking it to do instead of what you want (computers are SO

pesky that way...) said:
My SqL code looks as such.
SELECT Employee.emp_id, adecco.first_name, adecco.last_name, adecco.trc,
adecco.week_worked_date, Sum(adecco.pay_hours_adjusted) AS TotalHours
FROM Employee INNER JOIN adecco ON Employee.emp_id = adecco.emp_id
WHERE (((adecco.trc)="REG") AND
((adecco.week_worked_date)>DateAdd("ww",-13,Date())))
GROUP BY Employee.emp_id, adecco.first_name, adecco.last_name, adecco.trc,
adecco.week_worked_date
ORDER BY adecco.week_worked_date DESC;

Try

SELECT Employee.emp_id, adecco.first_name, adecco.last_name,
adecco.trc,
adecco.week_worked_date, Sum(adecco.pay_hours_adjusted) AS TotalHours
FROM Employee INNER JOIN adecco ON Employee.emp_id = adecco.emp_id
WHERE (((adecco.trc)="REG") AND
((adecco.week_worked_date)>DateAdd("ww",-13,Date())))
GROUP BY Employee.emp_id, adecco.first_name, adecco.last_name,
adecco.trc;

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

Jorge · Nov 30, 2004

Darn computers..

I tried your select John - I get -
"you tried to execute a query that does not include the specified expression
'work_week_date' as part of an aggregate function.

In a term widely used - HUH?

Jorge

John Vinson · Nov 30, 2004

Darn computers..

I tried your select John - I get -
"you tried to execute a query that does not include the specified expression
'work_week_date' as part of an aggregate function.

In a term widely used - HUH?

Hm. I don't have your database installed obviously, so I didn't
actually test this.

SELECT Employee.emp_id, adecco.first_name, adecco.last_name,
adecco.trc,
adecco.week_worked_date, Sum(adecco.pay_hours_adjusted) AS TotalHours
FROM Employee INNER JOIN adecco ON Employee.emp_id = adecco.emp_id
WHERE (((adecco.trc)="REG") AND
((adecco.week_worked_date)>DateAdd("ww",-13,Date())))
GROUP BY Employee.emp_id, adecco.first_name, adecco.last_name,
adecco.trc;

I guess I typo'd: is the field named work_week_date, or
week_work_date? In either case you should remove it from the SELECT
clause. If it's week_worked_date try

SELECT Employee.emp_id, adecco.first_name, adecco.last_name,
adecco.trc, Sum(adecco.pay_hours_adjusted) AS TotalHours
FROM Employee INNER JOIN adecco ON Employee.emp_id = adecco.emp_id
WHERE (((adecco.trc)="REG") AND
((adecco.week_worked_date)>DateAdd("ww",-13,Date())))
GROUP BY Employee.emp_id, adecco.first_name, adecco.last_name,
adecco.trc;

The SQL has the latter. Your message has the former. Is this the EXACT
SQL that you're using?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

Query proper results..?

Jorge

Steve Schapel

John Vinson

Jorge

Dave

John Vinson

Jorge

John Vinson