Top n Query

[Guest]

I have a table that has Sales People that we track performance on on a weekly
basis. I'm trying to develope a query that will show the top 20 sales people
grouped by date. So the result will have 20 people per day X 5 Days or a
total of 100 records. This is the only query I have been able to come up
with and it only shows the top 10 per day. I'm stumped.

SELECT TOP 100
[ISBH Work Sheet_Weekly].Date,
[ISBH Work Sheet_Weekly].SalesPerson_NAME,
Max([ISBH Work Sheet_Weekly].DailySalesTotal) AS MaxOfDailySalesTotal

FROM [ISBH Work Sheet_Weekly]

GROUP BY [ISBH Work Sheet_Weekly].Date, [ISBH Work
Sheet_Weekly].SalesPerson_NAME;

 

[John Spencer]

Perhaps the following will do what you want

SELECT T1.Date
,T1.SalesPerson_Name
,T1.DailySalesTotal
FROM [ISBH Work Sheet_Weekly] as T1
WHERE T1.DailySalesTotal IN
(SELECT TOP 20
Max(DailySalesTotal)
FROM [ISBH Work Sheet_Weekly] as T
WHERE T.Date = T1.Date
GROUP BY T.Date
ORDER BY Max(DailySalesTotal) DESC)

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Date is an Access reserved word and shouldn't be used as a column name.
Try adding a descriptor to the word date, e.g.: sales_date.

The following is untested:

PARAMETERS [Begin Date] Date, [End Date] Date;
SELECT [Date], SalesPerson_Name, DailySalesTotal
FROM [ISBH Work Sheet_Weekly] As T1
WHERE [Date] BETWEEN [Begin Date] And [End Date]
AND SalesPerson_Name IN
(SELECT TOP 20 SalesPerson_Name
FROM [ISBH Work Sheet_Weekly]
WHERE [Date] T1.[Date]
ORDER BY DailySalesTotal DESC)
ORDER BY [Date], DailySalesTotal Desc

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRK1yW4echKqOuFEgEQKFjQCeJyscQXGYf2LDfjgO2kqLYmFgNb0An2+M
kZm5B59Bhe7NtZzYr32QMOqF
=JPVL
-----END PGP SIGNATURE-----

 

[Guest]

Thank you very much for you help! It worked out awesome.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Date is an Access reserved word and shouldn't be used as a column name.
Try adding a descriptor to the word date, e.g.: sales_date.

The following is untested:

PARAMETERS [Begin Date] Date, [End Date] Date;
SELECT [Date], SalesPerson_Name, DailySalesTotal
FROM [ISBH Work Sheet_Weekly] As T1
WHERE [Date] BETWEEN [Begin Date] And [End Date]
AND SalesPerson_Name IN
(SELECT TOP 20 SalesPerson_Name
FROM [ISBH Work Sheet_Weekly]
WHERE [Date] T1.[Date]
ORDER BY DailySalesTotal DESC)
ORDER BY [Date], DailySalesTotal Desc

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRK1yW4echKqOuFEgEQKFjQCeJyscQXGYf2LDfjgO2kqLYmFgNb0An2+M
kZm5B59Bhe7NtZzYr32QMOqF
=JPVL
-----END PGP SIGNATURE-----

I have a table that has Sales People that we track performance on on a weekly
basis. I'm trying to develope a query that will show the top 20 sales people
grouped by date. So the result will have 20 people per day X 5 Days or a
total of 100 records. This is the only query I have been able to come up
with and it only shows the top 10 per day. I'm stumped.

SELECT TOP 100
[ISBH Work Sheet_Weekly].Date,
[ISBH Work Sheet_Weekly].SalesPerson_NAME,
Max([ISBH Work Sheet_Weekly].DailySalesTotal) AS MaxOfDailySalesTotal

FROM [ISBH Work Sheet_Weekly]

GROUP BY [ISBH Work Sheet_Weekly].Date, [ISBH Work
Sheet_Weekly].SalesPerson_NAME;

 

[Guest]

Thank you for taking the time to answer my question. It's very much
appreciated!!

Perhaps the following will do what you want

SELECT T1.Date
,T1.SalesPerson_Name
,T1.DailySalesTotal
FROM [ISBH Work Sheet_Weekly] as T1
WHERE T1.DailySalesTotal IN
(SELECT TOP 20
Max(DailySalesTotal)
FROM [ISBH Work Sheet_Weekly] as T
WHERE T.Date = T1.Date
GROUP BY T.Date
ORDER BY Max(DailySalesTotal) DESC)

I have a table that has Sales People that we track performance on on a
weekly
basis. I'm trying to develope a query that will show the top 20 sales
people
grouped by date. So the result will have 20 people per day X 5 Days or a
total of 100 records. This is the only query I have been able to come up
with and it only shows the top 10 per day. I'm stumped.

SELECT TOP 100
[ISBH Work Sheet_Weekly].Date,
[ISBH Work Sheet_Weekly].SalesPerson_NAME,
Max([ISBH Work Sheet_Weekly].DailySalesTotal) AS MaxOfDailySalesTotal

FROM [ISBH Work Sheet_Weekly]

GROUP BY [ISBH Work Sheet_Weekly].Date, [ISBH Work
Sheet_Weekly].SalesPerson_NAME;