Access 2007 Query with Totals on

[ldernst]

HELP.......
I am working in a table in access that has LOTS of lines so I want to do a query on 1 of 25 columns to get my total spend - so open my query, select my field name, and then put totals on and here is the error I keep getting:
Cannot group on fields selected with '*'. (Error 3121)
You tried to execute a SELECT statement that groups or totals all fields from all tables, selected with an asterisk ( * ).
Possible cause: You created an SQL statement that includes an aggregate function or GROUP BY clause that refers to a field you selected with an asterisk. This error occurs, for example, if you enter the following SQL statement:

• <code>SELECT * FROM Orders GROUP BY ShipVia;</code>

  
  <tbody>
  </tbody>

I do not understand what this is telling me because I do not see any fields with an * in it. This just recently started and actually I am unable to do any queries with TOTALS on because I get this error.

Thanks

 

[Joe4]

By using "SELECT *", you are telling Access to return ALL the fields in your Orders table (that is what the * means, return all the fields). However, you can use the * with an Aggregate Query (a query that has the "GROUP BY" phrase in it).

That is because in an Aggregate Query, every single field being returned has to be accounted for in one of two ways:
1. It needs to appear in the "GROUP BY" clause
2. It needs to show up in the "SELECT" clause with an aggregate function on it (like COUNT, SUM, etc.)

Here is an example of valid Aggregate Query syntax:
SELECT ID, Name, Count(OrderNumber), SUM(InvoiceAmount)
FROM Orders
GROUP BY ID, Name;

See how every field that is in the SELECT clause that does not have an Aggregate Function applied to it has to be listed in the GROUP BY clause?

 

[ldernst]

Hi Joe4,

I think I understand what you are say... the bizzare part is that i was not "Selecting *" when I drug down my field I only selected 1 out of 26 fields. What I did find though is the following:
- I got a new laptop and still had my old one kicking around - so I did a test on the old one to verify my sanity I did the test the exact same way as I have always done..... so I knew it had something to do with the new set up of my laptop .... so here is what I did:
1) click on the microsoft symbol, top left
2) go to Access Options
3) go to Object Designers
4) go to Query Design (section)
5) make sure there is NO checkmark beside "Output all fields"

- I had a checkmark there, so i UNCHECKED it and now my queries work like before - when I drag only 1 field down I can turn my TOTALS on and SUM it and not get an error.....
Does this make sense to you?

I truly appreciate you taking the time to reply to my problem

 

[Joe4]

I never new that setting existed before until you mentioned it until now. Sounds like you can set it to default that the queries always show all fields.
Can't imagine the need for that, when one can easily just drag down the asterisk, but I suppose they have their reasons.
Live and learn, I guess!

 

[ldernst]

Yep, that is exactly what is does - I can't imagine wanting all fields ALWAYS included either - that kinda defeats the purpose of a query doesn't it?
Ohhhhh the joys of a new PC and ensuring all of your settings remain status quo!
Thanks again for taking the time to read my question nonetheless