"you tried to execute a query that does not include the specified expression..."

[miconian]

I'm trying to run a query, and append the results to an existing table. I'm getting:

"You tried to execute a query that does not include the specified expression 'Year' as part of an aggregate function."

Here is the query:

INSERT INTO tbl_master_mysite( [Campaign ID], [IntraSite Site], [Year], [Month], [Confidence %], [As Of] )
SELECT [2012 Data from sep 10 2012].[Campaign ID], [2012 Data from sep 10 2012].[IntraSite Site], [2012 Data from sep 10 2012].Year, [2012 Data from sep 10 2012].Month, [2012 Data from sep 10 2012].[Confidence %], [2012 Data from sep 10 2012].[As Of]
FROM [2012 Data from sep 10 2012]
WHERE ((([2012 Data from sep 10 2012].Year)=2012))
GROUP BY [2012 Data from sep 10 2012].[Campaign ID], [2012 Data from sep 10 2012].[IntraSite Site], [2012 Data from sep 10 2012].Month, [2012 Data from sep 10 2012].[Confidence %], [2012 Data from sep 10 2012].[As Of]
HAVING ((([2012 Data from sep 10 2012].[IntraSite Site])="mysite.COM"));

I get the error *regardless* of whether or not the table to be appended to (tbl_master_mysite) contains the field 'Year.' Note that, in the query, 'Year' is a 'WHERE' parameter, so the year actually should not show up in the results anyway.

I realize that 'Year' is a reserved keyword in Access, but it's also the name of a field in the source data. I will have to go through this process hundreds of times, so I don't want to have to change the field name in the source data.

 

[xenou]

The error is telling you you must have the year field in the group by clause. Not sure but possibly this is what you want:

INSERT INTO tbl_master_mysite
    ( 
    [Campaign ID], 
    [IntraSite Site], 
    [Year], 
    [Month], 
    [Confidence %], 
    [As Of] 
    )
SELECT 
    t.[Campaign ID], 
    t.[IntraSite Site], 
    t.[Year], 
    t.[Month], 
    t.[Confidence %], 
    t.[As Of]
FROM 
    [2012 Data from sep 10 2012] AS t
WHERE 
    t.[Year]=2012
GROUP BY 
    t.[Campaign ID], 
    t.[IntraSite Site], 
[COLOR="#FF0000"]    t.[Year],[/COLOR]
    t.[Month], 
    t.[Confidence %], 
    t.[As Of]
HAVING 
t.[IntraSite Site]="mysite.COM";

However, there is no aggregate function in your query (Sum, Avg, Min, Max) so there's no need for grouping that I can see? Are you trying to remove duplicates? This should work too:

INSERT INTO tbl_master_mysite
    ( 
    [Campaign ID], 
    [IntraSite Site], 
    [Year], 
    [Month], 
    [Confidence %], 
    [As Of] 
    )
SELECT DISTINCT 
    t.[Campaign ID], 
    t.[IntraSite Site], 
    t.[Year], 
    t.[Month], 
    t.[Confidence %], 
    t.[As Of]
FROM 
    [2012 Data from sep 10 2012] AS t
WHERE 
    t.[Year]=2012
    AND
    t.[IntraSite Site]="mysite.COM";

If you have to use reserved words as field names at least *always* put them in brackets and hope for the best - Access is known to be buggy in such cases.

 

[miconian]

Thanks, xenou. Actually, it's supposed to be a sum query (on a field that it looks like I excluded), so I'm going to have to re-examine this.

 

[miconian]

Okay, I added the sum, and I'm still getting the same error about the 'year' field. Here's the current SQL.

INSERT INTO tbl_master_mysite( [Campaign ID], [IntraSite Site], [Month], [Confidence %], [As Of], [Net Amount], [Year] )
SELECT [2012 Data from sep 10 2012].[Campaign ID], [2012 Data from sep 10 2012].[IntraSite Site], [2012 Data from sep 10 2012].Month, [2012 Data from sep 10 2012].[Confidence %], [2012 Data from sep 10 2012].[As Of], Sum([2012 Data from sep 10 2012].[Net Amount]) AS [SumOfNet Amount], [2012 Data from sep 10 2012].Year
FROM [2012 Data from sep 10 2012]
WHERE ((([2012 Data from sep 10 2012].Year)=2012))
GROUP BY [2012 Data from sep 10 2012].[Campaign ID], [2012 Data from sep 10 2012].[IntraSite Site], [2012 Data from sep 10 2012].Month, [2012 Data from sep 10 2012].[Confidence %], [2012 Data from sep 10 2012].[As Of]
HAVING ((([2012 Data from sep 10 2012].[IntraSite Site])="mysite.COM"));