Totals Query Help!

[Jay]

Hi, I have a table with the following fields:

Manufacturer
Model
Value
SoldMonth

What I'm trying to do is write a query that will give me an average Value
for each month, but I want the average to be based only on records where the
Model is consistent across all months. So if Model x is missing from one or
more months all records for that model would be excluded from all the
overall Monthly averages. So the query will only average records where the
Model is present in each month.

Any help with this would be greatly appreciated.

Regards.....Jason

 

[Ken Snell \(MVP\)]

It'll help us if you can post some sample data, and show us what type of
result you want to see.

 

[John Spencer]

First of all what are your field types? Is month a text field or a date
field? How many months are you talking about? What is stored in Month?

Generically the idea would be something like the following.
Query1:
SELECT DISTINCT Manufacturer, Model, Month
FROM TableA

Query2:
SELECT Manufacturer, Model
FROM Query1
WHERE Month Between 1 and 12
GROUP BY Manufacturer, Model
HAVING Count(Month) = 12

Query3:
SELECT TableA.Manufacturer
, TableA.Model
, TableA.Month
, Avg(Value) as Average
FROM TableA INNER JOIN Query2
ON TableA.Manufacturer = Query2.Manufacturer
AND TableA.Model = Query2.Model
GROUP BY TableA.Manufacturer, TableA.Model

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jay]

Hi, My field types are all text, other than Value, which is a number. Some
of my data may look like:

Ford Mondeo 5750 Jun-06
Ford Fiesta 6000 Jul-07

I'm after just a list of averages per month (with the condition that the
averages are based on only records where the model is in each month at least
once).

Many thanks,

Jason