Group By Question

[aball65]

Rookie here. I have a table called CARS which contains two columns. Here what the table's data look like

Make Model
==== ====
Row 1: Ford Mustang
Row 2: Ford Maverick

I want to write a query which generates the following "grouped" result:

Make Model
==== ====
Ford Mustang
Maverick

Tried to use the following select statement with group by clause:

select make, model from CARS group by make;

but get an error like "...did not specify model in aggregate function".

Help please!

 

[David R]

This worked okay for me (built using the design view):

SELECT Make, Model
FROM tableAutomobiles
GROUP BY Make, Model
ORDER BY Make;

 

[aball65]

This didn't work with the SQL View. I don't know how to use the design view. I guess there is a difference between the two views??

 

[David R]

Obviously your table name will differ from mine, but I'll walk you through what I did in Design View (the one with the Square and Ruler symbol, in most versions of Access;

).

1. Queries tab of the Database Screen > New > Design View OR Create Query in Design view
2. Show Table dialog: select your CARS table, then click Add, then Close.
3. Add both the Make and Model fields to your query by double-clicking them in the grey top section of Design View. The grid below should add two entries.
4. Look for the Sigma (∑) button at the top of your screen; click it. The grid in the bottom half of Design View should add a new line, "Total:".
5. Under both of your fields, use the dropdown in the Total line to select "Group By".
6. Under both of your fields, use the dropdown in the Sort line to select "Ascending" (or whatever).
7. Preview your query; it should look like you expect.
8. Optionally, you can look at the SQL version then to see what was different from what you were doing before.
9. Profit!

 

[aball65]

Thanks a ton!