Rank records automatically

[max]

Let's say I have a table with the following fields. State, Customer,
and Total Sales. What I want to do is rank each customer by sales
volume by state. i.e. highest sales is ranked 1, second highest ranked
2, etc within each state.

I think I could do this in a cumbersome fashion by using the max
function, appending, deleting and using the max function again for #2
and so on. I was wondering if there is a built in function or a piece
of code or expression that would assign these rankings automatically in
a new field.

Which brings me to this. I guess I need to learn SQL. Are there any
web resources or books that anyone can recommend? Thanks

max

[salad]

max wrote:

Let's say I have a table with the following fields. State, Customer,
and Total Sales. What I want to do is rank each customer by sales
volume by state. i.e. highest sales is ranked 1, second highest ranked
2, etc within each state.

I think I could do this in a cumbersome fashion by using the max
function, appending, deleting and using the max function again for #2
and so on. I was wondering if there is a built in function or a piece
of code or expression that would assign these rankings automatically in
a new field.

Which brings me to this. I guess I need to learn SQL. Are there any
web resources or books that anyone can recommend? Thanks

max

I created a Continuous form on an employee table query. The two fields
to be displayed were EmployeeID and EmployeeName

I then added a new field. I gave it the name PosIt. It displays the
number as to the rank. In the ControlSource under the datatab I used
=GetPos([EmployeeID])

This calls the function GetPos. This is the code
Private Function GetPos(lngID As Long) As Long
Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "EmployeeID = " & lngID
GetPos = Me.RecordsetClone.AbsolutePosition + 1
rst.Close
Set rst = Nothing
End Function

Would something similar work OK for you? You'd need to modify the above
code and pass the customerID and State (in case the same customer is in
multiple states) to the function.

[Granny Spitz via AccessMonster.com]

max wrote:

Let's say I have a table with the following fields. State, Customer,
and Total Sales.

Do yourself a big favor and use only alphanumerics and the underscore for
names. Use the caption property to display something different for the user.
You avoid a lot of bugs that way.

What I want to do is rank each customer by sales
volume by state. i.e. highest sales is ranked 1, second highest ranked
2, etc within each state.

To rank records per group (State), use a correlated subquery:

SELECT (SELECT COUNT(*) + 1
FROM tblCustSales AS C
WHERE (C.State = CS.State AND C.TotalSales CS.TotalSales)) AS RankInState,

CS.State, CS.Customer, CS.TotalSales
FROM tblCustSales AS CS
ORDER BY CS.State, CS.TotalSales DESC;

I guess I need to learn SQL.

Yes, you do if you want to work with relational data to answer questions that
are more complex than can be designed with the QBE grid.

Are there any
web resources or books that anyone can recommend?

Web tutorials will give you the basics, but they don't teach you enough. You
need a book (or a few). "SQL Queries for Mere Mortals" by Michael Hernandez
and John Viescas is a good start.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

[max]

Thanks for your help. I ordered the book you recommended. Most
everything I do involves queries, so I guess it is long overdue to
learn SQL.
Granny Spitz via AccessMonster.com wrote:

max wrote:

Let's say I have a table with the following fields. State, Customer,
and Total Sales.

Do yourself a big favor and use only alphanumerics and the underscore for
names. Use the caption property to display something different for the user.
You avoid a lot of bugs that way.

What I want to do is rank each customer by sales
volume by state. i.e. highest sales is ranked 1, second highest ranked
2, etc within each state.

To rank records per group (State), use a correlated subquery:

SELECT (SELECT COUNT(*) + 1
FROM tblCustSales AS C
WHERE (C.State = CS.State AND C.TotalSales CS.TotalSales)) AS RankInState,

CS.State, CS.Customer, CS.TotalSales
FROM tblCustSales AS CS
ORDER BY CS.State, CS.TotalSales DESC;

I guess I need to learn SQL.

Yes, you do if you want to work with relational data to answer questions that
are more complex than can be designed with the QBE grid.

Are there any
web resources or books that anyone can recommend?

Web tutorials will give you the basics, but they don't teach you enough. You
need a book (or a few). "SQL Queries for Mere Mortals" by Michael Hernandez
and John Viescas is a good start.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1