Error: Cannot group on Fields selected with '*'.

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
'm getting Error: Cannot group on Fields selected with '*'. when trying to use sum query....
Why is this happening?
 
Pedie

Firstly, Access is not Excel.

It's actually much easier to do certain sums in Access.

What sort of SUMIF do you mean?

Do you want to group by month?

eg find out the sum for each mointh
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Upvote 0
My apologies if you take this as a duplicate Joe4...
Well this was related to query error...
And other was to find the faster way to do it without looping...:eeek::)
 
Upvote 0
Pedie

The way to do it without looping would be to use a query.

Can you explain what the criteria is and what you actually want to sum?
 
Upvote 0
Yes sure Norie...in table called AllExpDatas, i want to sum Expense_Amount field if
STD_ID = mycritiria1,
MyYear = mycritiria2,
Qv = mycritiria3,
Sites = mycritiria4,
StatusV = "Approved"

Thanks again for helping...if this works out my life will be much easier...:)
 
Upvote 0
Code:
select 
  sum( Expense_Amount ) 
from 
  AllExpDatas  
where 
(
  STD_ID = mycritiria1 
  and
  MyYear = mycritiria2
  and
  Qv = mycritiria3
  and
  Sites = mycritiria4
  and
  StatusV = 'Approved'
)
 
Upvote 0
James, thanks for quick reply...
This looks new to me though...
Do i just paste this code in the SQL View and run it after changing the critirias?:)

Thanks again.

Rich (BB code):
Rich (BB code):
select 
  sum( Expense_Amount ) 
from 
  AllExpDatas  
where 
(
  STD_ID = mycritiria1 
  and
  MyYear = mycritiria2
  and
  Qv = mycritiria3
  and
  Sites = mycritiria4
  and
  StatusV = 'Approved'
)

 
Upvote 0
yes, but be sure to wrap each criteria in the proper markers

dates have to have # around them
put single quotes (or double quotes) around text fields

Code:
  STD_ID = mycritiria1 
  and
  MyYear = #12/25/2011#
  and
  Qv = 'mycritiria3'
  and
  Sites = 'mycritiria4'
  and
  StatusV = 'Approved'
 
Upvote 0
James, wow..thats amaizing. Thank you very much...!

How can i write this kind of clean SQL [or whatever it is called...]
maybe a link....reference...in help! how do i learn to write one myself:)

Thanks again.
Pedie
 
Upvote 0
And to confirm, query is like excel formulas that gets updated by itself once my other datas in link table changes, is that correct?
 
Upvote 0

Forum statistics

Threads
1,224,598
Messages
6,179,820
Members
452,946
Latest member
JoseDavid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top