Executed query that doesnt include specified expression as an aggragated function (1 Viewer)

[davida1992]

Hey everyone, no idea why this is going wrong but could use some help!

I am receiving this error when i try to run a query:

"You tried to execute a query that doesnt include specified expression as an aggragated function"

There are two images attached which show the SQL view, the error and the design view.

If anyone can shed some light on this it woud be much appreciated!!

Thanks!

 

[pr2-eugin]

davida1992, all fields that are marked as "Expression" needs to be Changed to "Group By"..

 

[davida1992]

Thanks for that, the query now works, but i need it to group the results by the publisher, and then order by date.

The attachment shows what the SQL now looks like, however now it will not group and order how i want it to i assume?

Thanks!

 

[gemma-the-husky]

it can be very hard with totals queries

you would think you could group and total by some aspect, and then add another field as "information" for the group.

it doesn't work like that. Adding the extra field, changes the nature of the totals query, and you get a different number of rows.

this is why you cannot have Expression against the other field. it needs to be groupby, or sum, or count etc

 

[pr2-eugin]

I am afraid, I do not follow.. Showing the Query at this point is not helping. Is it possible for you to show a small sample data?

 

[davida1992]

I dont understand why thought because im not trying to do a totals query..

I just want the data from a table to be put into a report that is made from this query. The query first needs to group the data by the publisher, and then once it has organised by the publisher, it needs to order by the date of review.

Is there any way of doing this?

Thanks!

 

[pr2-eugin]

Group By comes under a Totals Query..

Why not try setting the Order by of the Report? As the Data is placed by the Query but I think the Reports perform a Sort of its own.. (I might be wrong here though!!)

 

[davida1992]

Still cannot get it to work :'( anyone have any more ideas...

Thanks :3

 

[gemma-the-husky]

what exactly are you trying to obtain from the query.

once you groupby publisher, all you can get is some statistical information of the other fields. you can do a count of game ref, which will identify how many games are from that publisher. you could set genre to br group by.

you would now get of count of games grouped by publisher and genre.

so
EA Sports 4
EA RPG 2
Sony Sports 2
Sony platform 1

you cannot add any more information to this, without changing the nature of the query. all the fields have to be selected to an aggregate function - which is what your error message is telling you

take your 2.png query. change all the expressions to groupby, except for the gameref - make that count

the order by date makes no sense at all. you are only showing totals, so the date is immaterial. if you include the date as grouby, you will just get counts off 1 against eberything - as no doubt you bought all the games on a different date.

maybe you do not want a totals query at all. get rid of the totals, sort the query as you want, and then add sort ascending/descending to get the query results into the right order.

just play around with it, to see what happens.

 

[Brianwarnock]

I dont understand why thought because im not trying to do a totals query..

I just want the data from a table to be put into a report that is made from this query. The query first needs to group the data by the publisher, and then once it has organised by the publisher, it needs to order by the date of review.

Is there any way of doing this?

Thanks!

All of this is done using the grouping and sorting facilities of a report

Brian

 

[boblarson]

All of this is done using the grouping and sorting facilities of a report

Brian

Yep, I was just about to post that.

 

[davida1992]

I did manage to find the grouping and sorting facilities, thanks for that Easier than i thought it would be!