Query Help for a beginner?

[Jason O]

Hi

If anyone could help I would greatly appreciate it. I have a table with the
following fields:

Car Model | Registration Year | Mileage | Price

I'm after a query to sort and group by model, then by registration year and
then to average the mileages and prices for each model & year.
I.e all average prices/mileages for all Ford Fiestas in 04, and the same for
all Ford Fiestas in 05 etc.... & the same for every other combination of
model & year. (along with a count of each)

I know it's probably simple and if anyone could point me in the right
direction I'd really appreciate it.

Regards

Jason
___

 

[Jeff C]

Hi

If anyone could help I would greatly appreciate it. I have a table with the
following fields:

Car Model | Registration Year | Mileage | Price

I'm after a query to sort and group by model, then by registration year and
then to average the mileages and prices for each model & year.
I.e all average prices/mileages for all Ford Fiestas in 04, and the same for
all Ford Fiestas in 05 etc.... & the same for every other combination of
model & year. (along with a count of each)

I know it's probably simple and if anyone could point me in the right
direction I'd really appreciate it.

Regards

Jason
___

Hi Jason
Create the query using the wizard and it gives you various options of
group by and order by.

If not, just add more to the orderby in the sql after the WHERE:

ORDER BY thistable.field1, thistable.field2, thistable.field3,
thistable.field4;


Jeff C

 

[John Spencer]

The SQL statement for this would look like

SELECT [Car Model], [Registration Year]
, Avg(Mileage) as AvgMiles
, Avg(Price) as AvgPrice
, Count([Car Model) as NumCars
FROM [Your Table Name]
GROUP BY [Car Model], [Registration Year]

If you can only use the query grid then
-- Open a new query
-- Select View: Totals from the Menu
-- Drag Car model to the grid twice
-- Drag Registration year, Mileage and Price to the grid once
-- Change Group By (in Totals) to Avg under Mileage and Price
-- Change Group by to Count under ONE of the Car Model fields