Solve this!!!

  • Thread starter Thread starter Mario
  • Start date Start date
M

Mario

I'm making a database in which I have to get a report like this

No. Name Pts.
1. 'name1' 70
1. 'name2' 70
1. 'name3' 70
4. 'name4' 69
4 'name5' 69
6. 'name6' 68
7. 'name7' 66
7. 'name8' 66
7. 'name9' 66


The names with the same points have to be equally rated
take a look a No. after 'name3', it begins with 4 and not 2
 
Mario said:
I'm making a database in which I have to get a report like this

No. Name Pts.
1. 'name1' 70
1. 'name2' 70
1. 'name3' 70
4. 'name4' 69
4 'name5' 69
6. 'name6' 68
7. 'name7' 66
7. 'name8' 66
7. 'name9' 66
Click to expand...


Use a subquery in the report's record source query:

SELECT table.personname, table.points,
(SELECT Count(*) + 1
FROM table As X
WHERE X.points < table.points
) As No
FROM table
 
I'm making a database in which I have to get a report like this

No. Name Pts.
1. 'name1' 70
1. 'name2' 70
1. 'name3' 70
4. 'name4' 69
4 'name5' 69
6. 'name6' 68
7. 'name7' 66
7. 'name8' 66
7. 'name9' 66


The names with the same points have to be equally rated
take a look a No. after 'name3', it begins with 4 and not 2
Click to expand...

Mario,
It's called 'Ranking'.
Here is a ranking example adapted from the sample Query database
available from the Microsoft Download center.

You'll need to adapt it to your needs, by using the fields in your
database.
Notice that YourTable is referred to as YourTable and also YourTable1.

Open a new query in design view.
Do NOT add the table to the QBE grid.

Click on the View tool button and select SQL.

Then paste the following into the SQL view window.

SELECT YourTable1.LastName, YourTable1.Points, (Select Count (*) From
YourTable Where [Points] < YourTable1.[Points] +1;) AS Rank
FROM YourTable AS YourTable1
ORDER BY YourTable1.Points;

Change YourTable to whatever the actual name of the table is. Keep the
1 after the table name wherever it appears in the above sample.
Note there is only one instance where the 1 does NOT appear.

Run the query.
Hope this helps.
 
Back
Top