Count - Ranking question...

[WindSailor]

1)
I have a query that has...

ID; Division; Category; Class; Amount; Place; Points

Division, Category,and Class are sorted in an ascending order with Amount sorted in a descending order.
An individual could be in several Divisions, Categories etc. with a different Amount.

I would like to either count or rank that persons tally with the column "Place".
I have tried =Count(*) but all I get is 1's straight down.
Any ideas?

2)
So far I can assign points using the IIf statement depending on the value in the "Place" column for each person, BUT, I need to save the values of "Place" and "Points" back to the table.
How do I do that?

I want to learn how to do this within a query...

Thanks

 

[Cosmos75]

1)
I have a query that has...

ID; Division; Category; Class; Amount; Place; Points

Division, Category,and Class are sorted in an ascending order with Amount sorted in a descending order.
An individual could be in several Divisions, Categories etc. with a different Amount.

I would like to either count or rank that persons tally with the column "Place".
I have tried =Count(*) but all I get is 1's straight down.
Any ideas?

Are you wanting to sum Amount and then rank ID by the sum of Amount? Or by Division/Category/Class? If you could show some sample data and the end result you want, that would be helpfull! What would be most helpfull, is if you could post a sample database file with some dummy data in it! (Delete all forms and queries, just leave your table stucture & relationships intact and be sure to compact the database to keep the size as small as possible when you zip the file)

2)
So far I can assign points using the IIf statement depending on the value in the "Place" column for each person, BUT, I need to save the values of "Place" and "Points" back to the table.
How do I do that?

Why do you want to do that? Generally, if it is something you need to recalculate later on, it would just done via a query. Are you trying to keep a history of people's ranking for future reference?

 

[WindSailor]

I have ID, Division, Category, Class, Amount, Place, Points.

ID (auto number); Division (sorted ascending); Category (sorted ascending); Class (sorted ascending); Amount (sorted descending - to show the highest amount first or at the top of the list); Place (to have a value returned here - zero being the default value); Points (to have a value returned here - zero being the default value).

OK...

INPUT

ID, Division, Category, Class, Amount, Place, Points.
1, A, AA, AAA, 150,0,0
2, A, AA, AAA, 200,0,0
3, A, AA, AAA, 250,0,0
4, B, AA, AAA, 350,0,0
5, B, AA, BBB, 125,0,0
6, A, BB, AAA, 175,0,0

OUTPUT

ID, Division, Category, Class, Amount, Place, Points.
3, A, AA, AAA, 250,1,10
2, A, AA, AAA, 200,2,5
1, A, AA, AAA, 150,3,3
6, A, BB, AAA, 175,1,10
4, B, AA, AAA, 350,1,10
5, B, AA, BBB, 125,1,10

This could be used in a sporting event, where you have divisions, categories, classes, and amount (if it was a timed event it "amount" would be in ascending order to reflect the lowest first). Or this configuration could be used in sales with the largest amount sold in the amount column, with the idea of listing the largest first, awarding a place value for that person, and then assigning points to that individual for his/her efforts AND being able to save that info back to the table so that I can use that info again to view their progress after a period of time.
This is with the thought that one employee could be in several Divisions, Categories etc.

I can't seem to get it to count in the "Place" column. I may be confusing the purpose of 'count' in this case. I tried the 'Top 5' scenario but it lists the top 5 in the whole query - it's not broke down to Division,Category,Class.

I have an IIf statement that I can use in the "Points" column that works on the value in the "Place" column but I can't get the "Place" column to work correctly to use that statement. If you have another way to assign points, please expand on that issue...

Finally, I need to save the aggregate values for the columns "Place" and "Points" back to the main table... that’s another issue that I don't know how to do... It just seems like this is a simple issue that is getting complicated real fast. I thought that Access would have something for a situation like this by default or functionality built in to perform this kind of action - it seems like a basic idea... I just don't know how to do it...

In the sample the fields have different names but the idea is the same...

Thanks

 

[WindSailor]

Just a thought…

The key I believe is in the ‘Amount’ column. How about having a function in the ‘Place’ column that states something like…
If ‘AmountNow’ is > previous record then
Value =10
End if
If ‘AmountNow’ is < previous record then
Value =5
End if
Etc… I’m not very good at this but I think you understand…

Possible?

 

[WindSailor]

It is late... and it just dawned on me what I just posted... oops...

I meant try to apply an IF statement or IIf statement to something similar to the one I used for 'Points'...

If 'AmountNow' is > previous record then
Value = 1
End If
If 'AmountNow' is < previous record then
Value = 2
End If

etc.

Possible?

I will try and expand on that tomorrow... or when I get that figured out.

 

[WindSailor]

Here is a SQL response someone gave me...

1. The first part, namely ordering the records is a single query,

SELECT ID, Division, Category, Class, Amount, Place, Points
FROM tblYourTable
ORDER BY Division, Category, Class, Amount DESC;

2. The next stage is to build a Rank/Place column - the logic is for any single record - count the number of records with an Amount >= my value, where the Div/Cat/Class are the same.

The psuedo SQL is...

SELECT Count(ID) FROM tblYourTable WHERE Division = My Division AND Category =My Category AND Class = My Class AND Amount >= My Amount

in the above the My..... refers to a single record

Now the trick is to link the second SQL to the first. I do this by using table aliases T1 for the table in the first query and T2 in the second

SELECT T1.ID, T1.Division, T1.Category, T1.Class, T1.Amount, (

SELECT Count(T2.ID) FROM tblYourTable AS T2 WHERE T2.Division = T1.Division AND T2.Category =T1.Category AND T2.Class = T1.Class AND T2.Amount >= T1.Amount
) AS Place

FROM tblYourTable AS T1
ORDER BY T1.Division, T1.Category, T1.Class, T1.Amount DESC;

The above is what is called a 'Corellated Query'

If you save this query as say Qry#1, and assuming that you have another table that gives the points for any place, eg:

tblPoints
Place Points
1 10
2 6
3 5
4 4
5 3
6 2
7 1
8 0
9 0

etc

then you can build a further query that displays the points alongside the place

SELECT T1.ID, T1.Division, T1.Category, T1.Class, T1.Amount, T2.Points
FROM Qry#1 AS T1 INNER JOIN tblPoints AS T2 ON T1.Place = T2.Place
ORDER BY T1.ID, T1.Division, T1.Category, T1.Class, T1.Amount DESC;

This should be the final result.

To save the results you will need to run 2 queries, one update query between tblYourTable and Qry#1 to set the Place and a second to set the Points.

These will look something like...

UPDATE tblYourTable AS T1 INNER JOIN Qry#1 AS T2 ON
T1.Division=T2.Division, T1.Category=T2.Category, T1.Class=T2.Class, T1.Amount=T2.Amount
SET T1.[Place] = T2.[Place];

UPDATE tblYourTable AS T1 INNER JOIN tblPoints AS T2 ON
T1.PLace = T2.Place
SET T1.[Points] = T2.[Points];

You will need to check table names and field names in all of the above...

 

[EMP]

In ranking records, you will invariably come to the situation of "ties" e.g. in one group, you had

Amount
150
150
140
140
130
130
120
110
110​

How would you allocate the places and points (using the queries)?