Filling Rank in a form's textbox from a table (1 Viewer)

[Wildboy99]

Hi Guys,

I have been serching on topic about rank, but mostly are using queries,
not exactly what I wish to do.

Here is where I am at:

Two tables, tblAddressBook and tblGameRecords.
[Name] in tblAddressBook is (one to many) linked to [Participant] in tblGameRecord

In a form: frmGameRecord,
main_form with date and game details,
sub_form with texbox: Participant, Score, Rank and ..................
after entering scores for all participants,
I wish to use command button to auto fill in Rank for all participants,
How can I get this done?
Any help will be much appreciated

PS. I have done report to show Rank of the results,
but it is not registered in the tblGameRecords.
I wish to register in the table as a record.

 

[plog]

Two tables, tblAddressBook and tblGameRecords.
[Name] in tblAddressBook is (one to many) linked to [Participant] in tblGameRecord

In a form: frmGameRecord,
main_form with date and game details,

First, your missing a table. You have a many to many relationship (many participants to many games). For this you need 3 tables:

tblAddress is good, but then you need a table for games (tblGames) which will hold the date and 'game details' that do not include participants or their scores. For that, you need a third table (tblGameScores).

With that your form system will work:, frmGameRecord will be based on tblGames and the subform will be based on tblGameScores.

For the ranking, what are the rules? And why must it be stored? Generally calculated values are calculated in a query and then when you need them you reference the query.

 

[isladogs]

This article shows one way of getting Rank order in queries.
The rank should not be saved permanently in a table as the values determining the rank order may change.

 

[Wildboy99]

First, your missing a table. You have a many to many relationship (many participants to many games). For this you need 3 tables:

tblAddress is good, but then you need a table for games (tblGames) which will hold the date and 'game details' that do not include participants or their scores. For that, you need a third table (tblGameScores).

With that your form system will work:, frmGameRecord will be based on tblGames and the subform will be based on tblGameScores.

For the ranking, what are the rules? And why must it be stored? Generally calculated values are calculated in a query and then when you need them you reference the query.

Many thanks for your reply.

I do have another table tblGamefield, that stores necessary game details,
Relationship is as follows:

One to many
Name(one name in tblAddressBook) to Participants(many games in tblGames)
GameField(one gamefield in tblGameField) to GameField(many gamefield in tblGames)

There is other criteria may change from time to time in the score calculation,
therefore, report from later date may show different results.
That is why I wish to record the rank data.

 

[isladogs]

Your comment about the report giving different results on a later date is precisely the point.
If its based on a query it will always show results based on the current data
However, whilst I disagree with doing so, you could create an update query to modify your table based on the rank order query

 

[Wildboy99]

Your comment about the report giving different results on a later date is precisely the point.
If its based on a query it will always show results based on the current data
However, whilst I disagree with doing so, you could create an update query to modify your table based on the rank order query

Thanks for suggetions

I have read through some threads about ranking in qury.
I need to record rank into the table, because later I need to use the rank as criteria for tblPrize
Apart for the top 3 prizes, there is also 7th, 17th, 27th.................
and also a prize for placed second to last.
In this case, using ranking query would be rather more comlicated

Any idea?

 

[isladogs]

See the last sentence in my previous reply. You need the query to calculate the rank order whether or not you then choose to store the rank values.
You could if you wish have a Prize field (text) and store e.g 1st, 7th, last in that field for appropriate records
In other words, you can assign prizes according to your specified criteria based on the rank …. whether or not it is stored

 

[Wildboy99]

See the last sentence in my previous reply. You need the query to calculate the rank order whether or not you then choose to store the rank values.
You could if you wish have a Prize field (text) and store e.g 1st, 7th, last in that field for appropriate records
In other words, you can assign prizes according to your specified criteria based on the rank …. whether or not it is stored

I see your point, the problem is,
how you you store the calculated rank order into table record?

And I have tried several ways of calculating rank in query,
been on it several days without success

Serialize method, system respond " unrecognized function"
Select(Count(*)............ All rank = 1
.............................................

 

[isladogs]

Copy the Serialize function into a standard module so it’s available to your app.
Create your rank order query and save it. Now create an update query linking your table and the rank order query by your ID field. Use that to update the rank field on your table...if you really must store the values in your table

 

[plog]

From what I've read, you do not need to store rank in a table. That value should live in a query.

Second, you've yet to explain the rules for ranking. So, now you need to demonstrate it with data. Please provide 2 sets of sample data:

A--startibg data from your tables. Include table and field names and enough data to cover all cases.

B---expected results. Show us the data you expect to end with when you start with the data in A.

 

[Wildboy99]

Here seems to have an answer:

http://www.lebans.com/DownloadFiles/rownumber.zip

I donwloaded it but can not open the file.
Has any of you guys opened this file before?

 

[isladogs]

The files on Stephen Lebans' site are very old and often in Access 97 format which can only be opened using A2010 or earlier
As you can see from Stephen's site http://lebans.com/rownumber.htm, this is exactly the same as the Serialize function used in my original link and which will do what you want.