CODEWITHSUNDEEP
sqlms-access
Marcellia
Marcellia

Reputation: 212

Does not include the specified expression as part of aggregate view - What does it mean?

I used the following code which Gives me the error

"Your query does not include the specified expression 'Remaining' as part of aggregate view"

SELECT
    [Insurance Policy Coverage].[Retention]-Sum([Net Invoice amount Approved]) AS Remaining,
    [Vendor Matter Detail].[Chubb Claim number], 
    [Insurance Policy Coverage].Retention, 
    [Vendor Matter Detail].[Insurance Policy Coverage ID]
FROM 
    (
        [List of Law Firms] INNER JOIN 
        (
            (
                [Database] INNER JOIN [Invoice Entries] ON 
                Database.[Short Name] = [Invoice Entries].[Short Name]
            )
            INNER JOIN [Invoice Payment Processing] ON 
            Database.[Short Name] = [Invoice Payment Processing].[Short Name]
        )
        ON 
        ([List of Law Firms].[Law Firm] = [Invoice Entries].Vendor) AND 
        ([List of Law Firms].[Law Firm] = Database.[Law Firm].Value)
    )
    INNER JOIN 
    (
        [Insurance Policy Coverage] INNER JOIN [Vendor Matter Detail] ON
        ([Insurance Policy Coverage].[Insurance Policy ID] = [Vendor Matter Detail].[Insurance Policy Coverage ID]) AND 
        ([Insurance Policy Coverage].[Insurance Policy ID] = [Vendor Matter Detail].[Insurance Policy Coverage ID]) AND 
        ([Insurance Policy Coverage].[Insurance Policy ID] = [Vendor Matter Detail].[Insurance Policy Coverage ID])
    )
    ON
    (Database.[Short Name] = [Vendor Matter Detail].[Short Name]) AND 
    (Database.[Short Name] = [Vendor Matter Detail].[Short Name])
GROUP BY 
    [Vendor Matter Detail].[Chubb Claim number], 
    [Insurance Policy Coverage].Retention, 
    [Vendor Matter Detail].[Insurance Policy Coverage ID];

However, I use a similar code for a simple mock database it works just fine - 

SELECT 
    [Insurance Coverage].[Retention Unit]-Sum([Net Invoice Amount]) AS Remaining, 
    [Matter Detail].[Claim Number], 
    [Insurance Coverage].[Retention Unit], 
    [Matter Detail].Policy
FROM 
    (
        (
            [Main Database] INNER JOIN [Matter Detail] 
            ON [Main Database].[Database ID] = [Matter Detail].[Short Name]
        ) 
        INNER JOIN [Payment Processing] ON 
        ([Main Database].[Database ID] = [Payment Processing].[Short Name]) AND
        ([Matter Detail].[Matter Detail ID] = [Payment Processing].[Matter Detail ID])
    ) 
    INNER JOIN [Insurance Coverage] ON 
    [Matter Detail].Policy = [Insurance Coverage].[Insurance ID]
GROUP BY 
    [Matter Detail].[Claim Number], 
    [Insurance Coverage].[Retention Unit], 
    [Matter Detail].Policy;

I am new to Microsoft Access - I have read several FAQ and the common conclusion was All fields in the SELECT field list must either be included in the GROUP BY clause or be included as arguments to an SQL aggregate function.

But, For me it seems like there is some other error; please advise the possible reasons for such error.

I appreciate your time and thank you in advance; let me know if you need additional information.

Upvotes: 2

Views: 62

Answers (1)

Marcellia
Marcellia

Reputation: 212

Problem Solved - One of the reason why My code was throwing the error was because of the unnecessary and complex relationships between tables. The Error stopped after I removed the unnecessarily links between the table - Here is the Final code I used 

SELECT [Insurance Policy Coverage].Retention-Sum([Net Invoice amount Approved]) AS Remaining, 
       [Vendor Matter Detail].[Chubb Claim number], 
       [Insurance Policy Coverage].Retention, 
       [Vendor Matter Detail].[Insurance Policy Coverage ID]
FROM 
       (
         [Insurance Policy Coverage] INNER JOIN [Vendor Matter Detail]  
        ON [Insurance Policy Coverage].[Insurance Policy ID] = [Vendor Matter Detail].[Insurance Policy Coverage ID]
        ) 
         INNER JOIN 
       (
          ([Database] INNER JOIN [Invoice Entries] 
           ON Database.[Short Name] = [Invoice Entries].[Short Name]
          ) 
          INNER JOIN [Invoice Payment Processing] 
     ON [Invoice Entries].[Invoice Entry ID] = [Invoice Payment Processing].[Invoice Entries ID]
       ) 
     ON [Vendor Matter Detail].[Vendor Matter ID] = [Invoice Entries].[Vendor Matter ID]
GROUP BY [Vendor Matter Detail].[Chubb Claim number], 
         [Insurance Policy Coverage].Retention, 
         [Vendor Matter Detail].[Insurance Policy Coverage ID];

Upvotes: 1

Related Questions