Auto-complete field in a table

[Onemike]

Hello everyone,

First of all, I would like to say that I'm new to Access but quickly learning
I have a database with 3 soccer teams and I would like to autocomplete a field with the position on the podium, based on the teams' scores.
For example, one field in a table contains the points, arranged in descending order: 80, 73, 53, and I want in another field, another table, for the database to autocomplete 1, 2, 3.

Is there a way to do that so it automatically updates itself if I add, let's say, 10 more teams with their own points in the future, so the positions will be 1, 2, 3 ... 13?

Thanks in advance!

Mike

 

[arnelgp]

you don't need another table, on my opinion.
what you need is a Query.

first create a Query that will "group" your scores (distinct, without duplicate):

(name it as, qryScoresGrouped)
select [scoreField] from yourTable group by [scoreField]

now create another query, again on your table to show the Ranking of each team:

select TeamName, [scoreField], DCount("1", "qryScoresGrouped", "[scoreField] >= " & [scoreField]) As Rank from yourTable;

if there are teams with same scores, they got same Rank.

 

[Onemike]

you don't need another table, on my opinion.
what you need is a Query.

first create a Query that will "group" your scores (distinct, without duplicate):

(name it as, qryScoresGrouped)
select [scoreField] from yourTable group by [scoreField]

now create another query, again on your table to show the Ranking of each team:

select TeamName, [scoreField], DCount("1", "qryScoresGrouped", "[scoreField] >= " & [scoreField]) As Rank from yourTable;

if there are teams with same scores, they got same Rank.

I really appreciate your help! There is some trouble with the synthax on my part. Based on your example, I got this:

SELECT Team_name, Score, DCount("1", "Performance", "[Score] >= " & [Score]) AS Podium FROM Team_name;

Can't figure out how to make it work

Mike

 

[arnelgp]

what is "Performance"? is it a query?
see this demo, see query qryScoresGrouped.
and seel Query1, the final query.

 

[Onemike]

what is "Performance"? is it a query?
see this demo, see query qryScoresGrouped.
and seel Query1, the final query.

Thank you so much, this solved my problem, I will adapt it to my own database. Cheers!