1

In this example Query,

SELECT [Account Transactions].ID, [Account Transactions].Transaction_Amount, *
FROM [Account Transactions]
GROUP BY [Account Transactions].ID, [Account Transactions].Transaction_Amount;

This Idea is to Group Account Transaction.ID's and capture the Sum of tracaction_amount for all transaction of the same ID.

The code was generated by Access 2016 and when executed I get:

Can not group on Fields with '*'

The interesting thing is that I can generate a report with the very same grouping and it works perfectly.

closed as off-topic by mustaccio, Md Haidar Ali Khan, John aka hot2use, Colin 't Hart, MDCCL Oct 4 '18 at 15:28

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – John aka hot2use, Colin 't Hart, MDCCL
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    If your idea is "to Group Account Transaction.ID's and capture the Sum of tracaction_amount" then your query is not doing that at all. – mustaccio Oct 2 '18 at 19:29
  • It's possible that part of the issue is the fact that, by including *, you've technically got two ID and two Transaction_Amount columns; the named ones, and the ones that are included as part of the * columns. – RDFozz Oct 3 '18 at 18:52
0

The grouping may be the same, but you have an asterisk (shortcut for select all field, but the sequence is unkown what prevents grouping and aggregation) at the end of your selected fields that causes the error.

If you want to get the sum of Transaction_Amount per ID you just group by ID not by Transaction_Amount

SELECT
    Sum([Account Transactions].Transaction_Amount) As SumAmount
  , [Account Transactions].ID
FROM
    [Account Transactions]
GROUP BY
    [Account Transactions].ID
  • Yes, I have discovered the proper answer to this issue and it's quite simple. – Dick Patton Oct 6 '18 at 18:33
  • Yes, I have discovered the proper answer to this issue and it's quite simple. The SQL created contains a ',*' view the SQL statement and remove those characters, it will now work. This is derived from the Query Property Sheet "Display All Fields' the others are Yes or No. if you select Yes, this tells access to show all filed in the tables found in the Query. Thus if you are doing a grouping Access will attempt to Group ALL fields in the Query, which in most case it can't thus the error. Cryptic as it is! – Dick Patton Oct 6 '18 at 18:42
0

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 list.

Note that 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 Transaction_Amount columns.

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 ID and 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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.