GROUP BY Clause causing problems

[mcgraw]

I have a query in Access 2007 that I am trying to run where it will only return 1 instance of the ID Field, and thought that GROUP BY was the way to go, but whenever I run it I am getting "You tried to execute a query that does not include the specified expression "Issue_ID" as part of an aggregate function."

Here's my query:
_________________
SELECT
Issues.ID, commenthistory.Issue_ID, commenthistory.Comment, commenthistory.Comment_Date, Issues.Title, Issues.[Assigned To], Issues.[Opened By], Issues.[Opened Date], Issues.Status, Issues.Priority, Issues.Description, Issues.Due_Date, Issues.[Related Issues], Issues.Comments, Issues.CMEMO, Issues.Model, Issues.Init_STE, Issues.Final_STE, Issues.TCON, Issues.Tech, Issues.Vendor, Issues.Location, Issues.Category

FROM Issues, commenthistory

WHERE ( ((([commenthistory]![Comment_Date]) Between Now() And Now()-7)) AND commenthistory.Issue_ID = Issues.ID)

GROUP BY (Issues.ID);
_______________
I tried putting a join between issues and commenthistory in the query builder, but also had problems with the Group by.

Any help would be greatly appreciated!

Thanks

 

[boblarson]

You have to include your other fields in the Select into the Group By as well.

 

[mcgraw]

You have to include your other fields in the Select into the Group By as well.

Ok, I'll admit that I am confused...if I want to group by only the issues.ID field, why do the others need to go in as well?

 

[boblarson]

Ok, I'll admit that I am confused...if I want to group by only the issues.ID field, why do the others need to go in as well?

It's just the way it is. If you go into the Query By Example grid, put in the fields you want and then select the Grouping button, you will see that all fields get the GROUP BY added and you can change some of them to EXPRESSION if they are or to WHERE if they are part of the where clause, but for the most part you just have to have them all as Group By because that is how the records group. They group by all fields that are there because all of the fields are applicable. If you want it ONLY grouped by a single field then you can only have that single field in your query.

 

[mcgraw]

Thanks! It worked. I don't fully understand HOW it worked, but it worked!

Thanks for the help!