This is touched on by ComputerVersteher's answer, but to address it more explicitly:
Access told you exactly what the problem was. You can't use
GROUP BY when you're using
* in your
SELECT * indicates to include every column in the table as of the time the statement is executed. In your case, you're actually including a second copy of the
ID and the
So, first - it's a bad idea to have multiple columns in your
SELECT list with the same name/alias. If you really want to have
Transaction_Amount included multiple times, it would be best to provide an alias, so the columns in the result set all have unique names.
SQL statements with a
GROUP BY have a restriction on what's in the
SELECT list. Every column in the
SELECT list should either by in the
GROUP BY list, or should be in an aggregate function (like
SUM()). By definition, none of the columns included by use of the
* can be in aggregate functions. You can't use
* in the
GROUP BY clause. Since you have to specify the columns explicitly in
GROUP BY, you would automatically have an invalid statement if any new column was added to the table. So, Access prevents you from using
SELECT * and
GROUP BY together.
Are you sure you didn't modify the code Access generated? I would be surprised if it would generate code it couldn't run.