CODEWITHSUNDEEP
sqlms-access
user1283776
user1283776

Reputation: 21764

Your query does not include the specified expression. What does it mean and how do I fix it?

When I add currency rate into this code Access tells me that "Your query does not include the specified expression [Transaction details].[Currency code] = [Currencies].[Currency code] as part of an aggregate function." What does it mean and how do I fix it?

SELECT [Sales structure].Cluster,
       [Sales structure].Country,
       [Transaction details].[Sales company code],
       [Sales structure].[Sales company name],
       Year([Transaction details].[YYYY-MM-DD])      AS [Year],
       Month([Transaction details].[YYYY-MM-DD])     AS [Month],
       Sum([Transaction details].[Sales volume LOC]) AS Sales,
       [Transaction details].[Customer code]
FROM   Currencies
       RIGHT JOIN ([Sales structure]
                   RIGHT JOIN [Transaction details]
                     ON [Sales structure].[Sales company code] = [Transaction details].[Sales company code])
         ON Currencies.[Currency code] = [Transaction details].[Customer code]
GROUP  BY [Sales structure].Cluster,
          [Sales structure].Country,
          [Transaction details].[Sales company code],
          [Sales structure].[Sales company name],
          Year([Transaction details].[YYYY-MM-DD]),
          Month([Transaction details].[YYYY-MM-DD]),
          [Transaction details].[Customer code],
          Currencies.[CUR/EUR]
HAVING (( [Transaction details].[Currency code] = [Currencies].[Currency code] ))
ORDER  BY Year([Transaction details].[YYYY-MM-DD]),
          Month([Transaction details].[YYYY-MM-DD]);

Upvotes: 0

Views: 1476

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

The problem is your having clause. You already have the condition in the on clause for the join, so it is unnecessary in the having clause:

SELECT [Sales structure].Cluster,
       [Sales structure].Country,
       [Transaction details].[Sales company code],
       [Sales structure].[Sales company name],
       Year([Transaction details].[YYYY-MM-DD])      AS [Year],
       Month([Transaction details].[YYYY-MM-DD])     AS [Month],
       Sum([Transaction details].[Sales volume LOC]) AS Sales,
       [Transaction details].[Customer code]
FROM   Currencies
       RIGHT JOIN ([Sales structure]
                   RIGHT JOIN [Transaction details]
                     ON [Sales structure].[Sales company code] = [Transaction details].[Sales company code])
         ON Currencies.[Currency code] = [Transaction details].[Customer code]
GROUP  BY [Sales structure].Cluster,
          [Sales structure].Country,
          [Transaction details].[Sales company code],
          [Sales structure].[Sales company name],
          Year([Transaction details].[YYYY-MM-DD]),
          Month([Transaction details].[YYYY-MM-DD]),
          [Transaction details].[Customer code],
          Currencies.[CUR/EUR]
ORDER  BY Year([Transaction details].[YYYY-MM-DD]),
          Month([Transaction details].[YYYY-MM-DD]);

The specific issue is that the currency codes are not in the group by clause and they are not surrounded by aggregation functions in the having clause. In fact, you might want to include the currency in the select and group by clauses, but that is another issue.

Upvotes: 3

Related Questions