Average numeric field (PAY) for specific day of week in Access que

D

dapjr

Database has thousands of pay records over past 8 years. I want to see
average salary earned on Mondays only. Salary field is called [PAY], date
field is called [DAY]. Please send response to [email protected] - Thank
you.
 
D

Duane Hookom

Try this expression:
Sum(Abs(Weekday([DAY]) = 2) * [PAY]) /Sum(Abs(Weekday([DAY]) = 2))

Make sure your returned records include at least one Monday date.
 
J

John Vinson

Database has thousands of pay records over past 8 years. I want to see
average salary earned on Mondays only. Salary field is called [PAY], date
field is called [DAY]. Please send response to [email protected] - Thank
you.
Click to expand...

A Totals query should do this, using DatePart to extract just the
Monday data:

SELECT Avg([PAY])
FROM tablename
WHERE DatePart("w", [DAY]) = 2;

Note that these newsgroups are for the benefit of the public. Private
email support is for paying customers (like many of the volunteers
here I'm in business as a consultant, donating my time on the groups),
and requesting it is considered impolite; if it's worth my time to
reply, it should be worth your time to come back to the newsgroup for
the answer. Please reply to the newsgroup; a reply by EMail will be
considered a proposal to initiate a paid consulting project.


John W. Vinson[MVP]
 
M

Mike Schlosser

dapjr said:
Database has thousands of pay records over past 8 years. I want to see
average salary earned on Mondays only. Salary field is called [PAY], date
field is called [DAY]. Please send response to [email protected] - Thank
you.
Click to expand...


If DAY is a date field use this

SELECT Avg(PAY) AS AvgOfPay
FROM EnterYourTableNameHere
WHERE Weekday([DAY])=2;


Adjust the criteria if DAY is not a date field.
Notice that Weekday([DAY])=2 is Monday. Sunday = 1. Change the number to
view other days.

If DAY contains "Mon", "Tue", etc then use

SELECT Avg(PAY) AS AvgOfPay
FROM EnterYourTableNameHere
WHERE [DAY]="Mon";


Mike Schlosser
 
Top