Count not working

  • Thread starter Thread starter Rick Campbell
  • Start date Start date
R

Rick Campbell

I'm losing my mind over this. I've stripped my query down to the barebones
and it is still counting wrong.

Here's the query:

SELECT DISTINCTROW Sales.Style, CityZipLookUp.City,
Format$(Sales.SoldDate,'yyyy') AS [SoldDate By Year], Avg(Sales.SoldPrice)
AS [Avg Of SoldPrice], Count(*) AS [Count Of Sales]
FROM CityZipLookUp INNER JOIN Sales ON CityZipLookUp.City = Sales.City
GROUP BY Sales.Style, CityZipLookUp.City, Format$(Sales.SoldDate,'yyyy'),
Year(Sales.SoldDate);

The tables Sales has property sales records from 1998 through 2002. What I'm
getting, on some cities but not all, is an anomolous number of sales for
2002. Most of the anomolies are lower, with one city showing an impossibly
high number of sales.

I've done this without the CityZipLookUp join and it still happens.

Appreciate any and all help.

P.S. Yes, the Sales table has all the data.

TIA

Rick
 
Rick,

Try it like:

SELECT Sales.Style
, CityZipLookUp.City
, Format$(Sales.SoldDate,'yyyy') AS [SoldDate By Year]
, Avg(Sales.SoldPrice) AS [Avg Of SoldPrice]
, Count(*) AS [Count Of Sales]
FROM CityZipLookUp
INNER JOIN Sales ON CityZipLookUp.City = Sales.City
GROUP BY Sales.Style
, CityZipLookUp.City
, Format$(Sales.SoldDate,'yyyy')


--
HTH

Dale Fye


I'm losing my mind over this. I've stripped my query down to the
barebones
and it is still counting wrong.

Here's the query:

SELECT DISTINCTROW Sales.Style, CityZipLookUp.City,
Format$(Sales.SoldDate,'yyyy') AS [SoldDate By Year],
Avg(Sales.SoldPrice)
AS [Avg Of SoldPrice], Count(*) AS [Count Of Sales]
FROM CityZipLookUp INNER JOIN Sales ON CityZipLookUp.City = Sales.City
GROUP BY Sales.Style, CityZipLookUp.City,
Format$(Sales.SoldDate,'yyyy'),
Year(Sales.SoldDate);

The tables Sales has property sales records from 1998 through 2002.
What I'm
getting, on some cities but not all, is an anomolous number of sales
for
2002. Most of the anomolies are lower, with one city showing an
impossibly
high number of sales.

I've done this without the CityZipLookUp join and it still happens.

Appreciate any and all help.

P.S. Yes, the Sales table has all the data.

TIA

Rick
 
Dale,

The problem is in the city field. I am trying to get a lookup table to
bypass the city field in table Sales because it is not accurate. I need the
ZipCode field in Sales to lookup the City field in the CityZipLookUp table.
Any idea on what I"m doing wrong there?

Thanks for your help.

Rick
 
Back
Top