Can't get insert into to work

[juschuma]

Here's my insert into statement:

INSERT INTO tbl_2002YTDAmounts_V2
SELECT [GL_data].[VendorName] AS VendorName, [GL_data].[PS_Num] AS PS_Num, [GL_data].[VendorID] AS VendorID, [GL_data].[GL_Num] AS GL_Num, sum([GL_data].[2002YTDAmount]) AS 2002YTDAmount
FROM GL_data
GROUP BY [GL_data].[GL_Num], [GL_data].[PS_Num], [GL_data].[VendorID];

Both tables tbl_2002_YTDAmounts_V2 and GL_data have fields named the same. GL_data has an additional date field that I'm not bringing into tbl_2002YTDAmounts_V2. That's why I'm grouping, so I can sum up the all the amounts from different dates.

I get the following error message:

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

I've tried duplicating this insert query for a couple of simple test tables and it works fine.

Any thoughts?

 

[wemeier]

You have a SUM expression in your query. This makes the query an AGGREGATE query. When that happens, every field must have an AGGREGATE function assigned to it (First, Last, Sum, Count, Min, Max, etc.).

Try modifying your INSERT as follows:

INSERT INTO tbl_2002YTDAmounts_V2
SELECT First([GL_data].[VendorName]) AS VendorName, First([GL_data].[PS_Num]) AS PS_Num, First([GL_data].[VendorID]) AS VendorID, First([GL_data].[GL_Num]) AS GL_Num, sum([GL_data].[2002YTDAmount]) AS 2002YTDAmount
FROM GL_data
GROUP BY [GL_data].[GL_Num], [GL_data].[PS_Num], [GL_data].[VendorID];

 

[cmmrfrds]

It looks like you can just add the vendor name to the group by, it is redundant with id but that is okay in order to accomplish what wemeier pointed out.

INSERT INTO tbl_2002YTDAmounts_V2
SELECT [GL_data].[VendorName] AS VendorName, [GL_data].[PS_Num] AS PS_Num, [GL_data].[VendorID] AS VendorID, [GL_data].[GL_Num] AS GL_Num, sum([GL_data].[2002YTDAmount]) AS 2002YTDAmount
FROM GL_data
GROUP BY [GL_data].[GL_Num], [GL_data].[PS_Num], [GL_data].[VendorID], [GL_data].[VendorName] ;