Count unique values in a field

[Jim]

Hi,
I need an expression that counts the number of unique
values in the field of a query. I want to calculate the
average sales amount over a given time span for each sales
rep, so I need to know the number of "Fiscal Years" over a
given time span. (For example, from 1997 to 2004 the
number would be 8). The query fields are "Fiscal
Year", "Project Name", "Sales Amount" and "Sales Rep".
A "Sales Rep" would have several "Project Names" with
corresponding "Sales Amounts" in a given "Fiscal Year".
Thanks

 

[Chris2]

Hi,
I need an expression that counts the number of unique
values in the field of a query. I want to calculate the
average sales amount over a given time span for each sales
rep, so I need to know the number of "Fiscal Years" over a
given time span. (For example, from 1997 to 2004 the
number would be 8). The query fields are "Fiscal
Year", "Project Name", "Sales Amount" and "Sales Rep".
A "Sales Rep" would have several "Project Names" with
corresponding "Sales Amounts" in a given "Fiscal Year".
Thanks

Seems to be two questions:

I need an expression that counts the number of unique
values in the field of a query.

.. . . and . . .

I want to calculate the average sales amount over a
given time span for each sales rep

Making many assumptions . . .


CREATE TABLE RepresentativeSales
(SaleID INTEGER
,SalesRep CHAR(12)
,FiscalYear DATE
,ProjectName CHAR(36)
,SalesAmount CURRENCY
,CONSTRAINT pk_RepresentativeSales PRIMARY KEY (SaleID)
)

Sample Data

1, Mike, #01/01/1996#, "Bluetooth", 50000.00
2, Sally, #01/01/1997#, "Captain Crunch", 100000.00
3, Sally, #01/01/1998#, "Clusters", 25000.00
4, Sally, #01/01/1999#, "Honey Oats", 50000.00
5, Sally, #01/01/2000#, "Fruit Loops", 50000.00
6, Dave, #01/01/2003#, "Hondas", 250000.00
7, Dave, #01/01/2004#, "Nissan", 200000.00



Question 1:

I don't know for sure which column needs to have unique values counted,
but I'll assume SalesAmount. This shows how many times any particular
SalesAmount value appears.

SELECT R1.SalesAmount
,COUNT(R1.SalesAmount)
FROM RepresentativeSales AS R1
GROUP BY R1.SalesAmount

If you only want cases where *unique* SalesAmounts appear, then we do:

SELECT R1.SalesAmount
,COUNT(R1.SalesAmount)
FROM RepresentativeSales AS R1
GROUP BY R1.SalesAmount
HAVING COUNT(R1.SalesAmount) = 1



Question 2:

SELECT R1.SalesRep
,AVG(R1.SalesAmount) as AverageSales
FROM RepresentativeSales AS R1
WHERE YEAR(R1.FiscalYear) BETWEEN 1997 and 2004
GROUP BY R1.SalesRep