Top 10 query

  • Thread starter Thread starter Hanksor
  • Start date Start date
H

Hanksor

I have a table that has customer summary info with [Totals] for 4
years(2002,2003,2004,2005). I need to create a query that gives me the top
10 [Totals] for each year. Is this possible? Any help will be appreciated.
 
It worked, sort of. I doesn't give me the top 10 though. It returns all
records.

The query consists of;
[Customers].[CustNo],[tblSalesInfo].[Year],[tblSalesInfo].[Total]

I put this code in the criteria section of the Total column in the query;

In (Select Top 10 [Total] From tblSalesInfo Where
[CustNo]=[Customers].[CustNo] Order By [Total] DESC)

I have two tables.
[Customers]
CustNo
CustomerName and
[tblSalesInfo]
Year
Total
Click to expand...
I would like to be able to return the top 10 Total for each year. Any help
will be appreciated.
 
You didn't say this, but for this to work I have to assume you have a CustNo
field in the tblSalesInfo table.

[Customers].[CustNo],[tblSalesInfo].[Year],[tblSalesInfo].[Total]

Try this code in the criteria section of the Total column in the query

In (Select Top 10 S.[Total]
FROM tblSalesInfo AS S
Where S.[Year] = [TblSalesInfo.[Year]
Order By S.[Total] DESC)

Assumption is that your entrie SQL would look like

SELECT [Customers].[CustNo], [tblSalesInfo].[Year], [tblSalesInfo].[Total]
FROM Customers INNER JOIN tblSalesInfo
ON Customers.CustNo = tblSalesInfo.CustNo
WHERE tblSalesInfo.Total In
(Select Top 10 S.[Total]
FROM tblSalesInfo AS S
Where S.[Year] = [TblSalesInfo.[Year]
Order By S.[Total] DESC)

Hanksor said:
It worked, sort of. I doesn't give me the top 10 though. It returns all
records.

The query consists of;
[Customers].[CustNo],[tblSalesInfo].[Year],[tblSalesInfo].[Total]

I put this code in the criteria section of the Total column in the query;

In (Select Top 10 [Total] From tblSalesInfo Where
[CustNo]=[Customers].[CustNo] Order By [Total] DESC)

I have two tables.
[Customers]
CustNo
CustomerName and
[tblSalesInfo]
Year
Total
Click to expand...
I would like to be able to return the top 10 Total for each year. Any help
will be appreciated.


David S via AccessMonster.com said:
Yes, it is possible: see http://support.microsoft.com/?kbid=210039
Click to expand...
Click to expand...
 
Thanks John!! It worked. A little slow, but it worked non the less.......

John Spencer said:
You didn't say this, but for this to work I have to assume you have a CustNo
field in the tblSalesInfo table.

[Customers].[CustNo],[tblSalesInfo].[Year],[tblSalesInfo].[Total]

Try this code in the criteria section of the Total column in the query

In (Select Top 10 S.[Total]
FROM tblSalesInfo AS S
Where S.[Year] = [TblSalesInfo.[Year]
Order By S.[Total] DESC)

Assumption is that your entrie SQL would look like

SELECT [Customers].[CustNo], [tblSalesInfo].[Year], [tblSalesInfo].[Total]
FROM Customers INNER JOIN tblSalesInfo
ON Customers.CustNo = tblSalesInfo.CustNo
WHERE tblSalesInfo.Total In
(Select Top 10 S.[Total]
FROM tblSalesInfo AS S
Where S.[Year] = [TblSalesInfo.[Year]
Order By S.[Total] DESC)

Hanksor said:
It worked, sort of. I doesn't give me the top 10 though. It returns all
records.

The query consists of;
[Customers].[CustNo],[tblSalesInfo].[Year],[tblSalesInfo].[Total]

I put this code in the criteria section of the Total column in the query;

In (Select Top 10 [Total] From tblSalesInfo Where
[CustNo]=[Customers].[CustNo] Order By [Total] DESC)

I have two tables.
[Customers]
CustNo
CustomerName and
[tblSalesInfo]
Year
Total
Click to expand...
I would like to be able to return the top 10 Total for each year. Any help
will be appreciated.


David S via AccessMonster.com said:
Yes, it is possible: see http://support.microsoft.com/?kbid=210039
Click to expand...
Click to expand...
Click to expand...
 
Back
Top