How to average only fields whose records meet certain criteria in Access?

Question

I have an ACCESS table named Reviewer Score Averages containing information on company reviews done by individuals, structured like this:

ID | Reviewer | Company | ReviewScore

The Company field can only hold four values:

STARBUCKS
MCDONALDS
GREENMOUNTAIN
CARIBOU

We have an SQL query that averages all the ReviewScores for a given Reviewer, but want to modify it so that it also calculates an average of all ReviewScores for Companies other than CARIBOU.

The original, functional query is:

SELECT [Reviewer Score Averages].Reviewer, 
       Reviewers.[Last Name] & ", " & [First Name] AS Name, 
       Reviewers.[Email Address], 
       Avg([Reviewer Score Averages].[1stReviewScore]) AS AvgOfAllReviews;

The edited version, that so far does not work, is:

SELECT [Reviewer Score Averages].Reviewer, 
       Reviewers.[Last Name] & ", " & [First Name] AS Name, 
       Reviewers.[Email Address], 
       Avg([Reviewer Score Averages].[1stReviewScore]) AS AvgOfAllReviews, 
       Avg([Reviewer Score Averages].[1stReviewScore] 
WHERE [Reviewer Score Averages].[Company] = 'STARBUCKS' OR 'MCDONALDS' OR 'GREENMOUNTAIN') AS AvgOfNonCaribou;

When I try to run this code, I get an error message that I am missing an operator: I've looked at Microsoft's documentation for WHERE syntax, but I don't see anything on it that I'm obviously missing.

This is a table in an Access 2003 database.

What am I doing wrong? Am I misusing WHERE? Is my syntax wrong? Am I missing something? Is there a better way to approach the problem?

Thanks very much for your help.

We are missing some details, notably the `FROM` clause, which would reveal how tables `[Reviewer Score Averages]` and `Reviewers` may be joined, and the `GROUP BY` clause would be helpful too. Ideally, you'd post table schema, test data and expected results :) — onedaywhen, Dec 16 '11 at 09:32

Joe Stefanelli · Answer 1 · 2011-12-15T19:26:33.290

1

Why not just do:

...WHERE [Reviewer Score Averages].[Company] <> 'CARIBOU'

or, to make your original work:

...WHERE [Reviewer Score Averages].[Company] = 'STARBUCKS' 
      OR [Reviewer Score Averages].[Company] = 'MCDONALDS' 
      OR [Reviewer Score Averages].[Company] = 'GREENMOUNTAIN'

EDIT: Fleshing out @Remou's comment:

...WHERE [Reviewer Score Averages].[Company] IN ('STARBUCKS', 'MCDONALDS', 'GREENMOUNTAIN')

edited Dec 15 '11 at 19:26

answered Dec 15 '11 at 19:04

Joe Stefanelli

132,803
19
237
235

Access is happy enough with IN, as well. – Fionnuala Dec 15 '11 at 19:22
Good calls both - these are more elegant than the code I had originally, and fix part of my syntax problem. I think, though, that sfuqua is right that the more fundamental problem is the WHERE clause inside the AVG clause. – Chris MacDonald Dec 15 '11 at 20:42

score 1 · Accepted Answer · answered Dec 15 '11 at 19:59

The missing operator is definitely in WHERE [Reviewer Score Averages].[Company] = 'STARBUCKS' OR 'MCDONALDS' OR 'GREENMOUNTAIN'; as others have pointed out, you need the IN operator in this case: WHERE [Reviewer Score Averages].[Company] IN ('STARBUCKS', 'MCDONALDS' , 'GREENMOUNTAIN').

However, I don't believe you can put a WHERE clause inside of the AVG clause. I don't have Access 2003 so I can't confirm this for you, but in 2010 this gives the desired result and will help you figure out the right syntax for your real tables:

SELECT AVG(ReviewScore), AVG(SWITCH(COMPANY = 'Caribou', NULL, 1=1, ReviewScore)) as NotCaribou
FROM table1

I think this is it: SWITCH appears to be the way to go. My actual code for the real table I'm working on is still buggy for other reasons, but I've tested this chunk with the Coffee database I set up as an example and it works like a dream. Thanks a lot for your help, @sfuqua. — Chris MacDonald, Dec 15 '11 at 20:47

score 0 · Answer 3 · edited May 23 '17 at 12:11

Rather than using SWTICH inline with the AVG function, consider using two views e.g.

CREATE VIEW ReviewsAvgsAll
AS
SELECT Reviewer, 
       Avg([1stReviewScore]) AS AvgOfReviews
  FROM [Reviewer Score Averages] AS RSA
 GROUP 
    BY Reviewer;

CREATE VIEW ReviewsAvgsNonCARIBOU
AS
SELECT Reviewer, 
       Avg([1stReviewScore]) AS AvgOfReviews
  FROM [Reviewer Score Averages] AS RSA
 WHERE Company <> 'CARIBOU';
 GROUP 
    BY Reviewer;

Create these VIEWs in the usual way; note the CREATE VIEW syntax requires ANSI-92 Query Syntax.

Then join Reviewers to ReviewsAvgsAll and semi-join to ReviewsAvgsNonCARIBOU in the usual way. You've omitted your FROM clause but assuming the common attribute is Reviewer it could look something like this:

SELECT RAA.Reviewer, 
       R.[Last Name] & ", " & [First Name] AS Name, 
       R.[Email Address], 
       RAA.AvgOfReviews AS AvgOfAllReviews, 
       RAN.AvgOfReviews AS AvgOfNonCARIBOUReviews
  FROM Reviewers AS R       
       INNER JOIN ReviewsAvgsAll AS RAA
          ON R.Reviewer = RA.Reviewer
       INNER JOIN ReviewsAvgsNonCARIBOU AS RAN
          ON R.Reviewer = RAN.Reviewer
UNION
SELECT RAA.Reviewer, 
       R.[Last Name] & ", " & [First Name] AS Name, 
       R.[Email Address], 
       RAA.AvgOfReviews AS AvgOfAllReviews, 
       -1 AS AvgOfNonCARIBOUReviews
  FROM Reviewers AS R       
       INNER JOIN ReviewsAvgsAll AS RAA
          ON R.Reviewer = RA.Reviewer
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM ReviewsAvgsNonCARIBOU AS RAN
                    WHERE R.Reviewer = RAN.Reviewer
                  );

How to average only fields whose records meet certain criteria in Access?

3 Answers3