CODEWITHSUNDEEP
sqlms-access
Chris MacDonald
Chris MacDonald

Reputation: 13

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

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.

Upvotes: 1

Views: 2441

Answers (3)

onedaywhen
onedaywhen

Reputation: 57023

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
                  );

Upvotes: 0

sfuqua
sfuqua

Reputation: 5853

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

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

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')

Upvotes: 1

Related Questions