Can't get GROUP BY to work

[GraeagleBill]

I want to display a list of descriptions taken from a table, "tblRegister". I'm attempting to use the GROUP BY phrase to suppress duplication. Below is a screenshot of the SQL view of the query and the diagnostic that accompanies my last attempt.





What's the problem here?

[aytee111]

It looks like you typed that last bit in. Rather let Access do it for you. Remove that line, go to Design view and click on the Totals icon. Then you will see what to fill in.

[JoeM]

A few things:

1. When using Aggregate Queries, every field in your SELECT clause must either be "GROUPED" (show up in the "GROUP BY" clause), or have an Aggregate Function applied to it (i.e. SUM, MAX, etc).
So you have three fields in your SELECT clause that you need to do something with.

2. ORDER BY comes at the very end, after the GROUP BY clause.

[GraeagleBill]

Following JoeM suggestion I created the following. The query runs as desired. I want to run the query as the RowSource of a combo box. How do I filter the "TDate" and "TTypeID" in the query............ other than building the SQL in code and updating the query def?

[GraeagleBill]

Oops! The "Count" design view is by mistake and should not have been included.

[GraeagleBill]

Oops! The "Count" design view is by mistake and should not have been included.

[GraeagleBill]

I'll take the SQL generated by the query design and make the appropriate substitutions for "TDate" and "TTypeID" in code and set the RowSource for the combo box.

Thanks for the help,
Bill