Re-ranking records in tables

[stevegee76]

Hi.

I'm not sure what I'm about to describe can actually be done(easily) but here goes anyway.

I have a db which has a form - frmMainForm - bound to a table - SWDataT. In this table are what I refer to as the static values for the condition of roads in an area. Each road has a static value score - StaticScore - and a ranking for each road has been assigned based on the static value score, highest being rank 1, lowest rank 215 out of 215 roads - SWRankingScore.

The db user selects a particular road from the list and DLookups fill in text fields on the form from the static values for that particular road.

The user then clicks a button which takes them to a new form - frmDynamicScore - to calculate the dynamic values for the road. This new form is bound to another table - ScoreT. The button has some code which opens the frmDynamicScore Form and populates the roads ID number - VNumber, road name and static score currently displayed on the Main form into fields in the ScoreT table and checks to see if there is already a record for this road in the ScoreT table based on road ID number and if not it makes a new record for this Id Number.

The dynamic values are then calculated and the static value is multiplied by reduction factors for various dynamic scoring factors to make a new score called the Dynamic Score. This is populated into a field called DynamicScore in the ScoreT table.

What I now need to do is to recaluate this roads rank in relation to the other roads in this area based on the new dynamic score. However, not all roads will have been given a dynamic score yet, so i need to rerank by looking first at the dynamic scores where they exist for a road and if not the original static score. I would also like to display this new ranking score on the frmDynamicScore Form if possible.

Does anyone have any suggestions about how this reranking can be done?
I have looked up help on this for the last day or so but I can't find any help for this specific issue.

Any help is greatly appreciated.

Thanks Steve

 

[spikepl]

Untested thoughts:

You could make a query that gets the ID and DynamicScore
And then a query that gets the ids from the static data that do not appear in the dynamic table, and their scores
Then make a union query based on those two and then you have all ids and the scores

another way, perhaps thesimplest, would be to construct a query based on the static table right joined to the dynamic table, to show the id and static score, and then have one more field in the query with the dynamic score, grabbed from the dynamic table, which would be null, if not existing. Add yet another column, and in that column put an IIF function, that takes the dynamic score if not null, or else the static score. This gives you your new score

 

[stevegee76]

Hi,
thanks for replying. I have found a way to do this also using queries and following the example in MS Supports 'Northwind' example database. It also uses queries. This method also uses queries which is where I was having trouble finding information as I was trying to simply do some coding to give me the answer straight into the ScoreT table .

The query method works fine. If anyone is interested here is the link to the Northwind example:
(Note, I have had to replace the .'s with -'s as I was getting a message that said i could not post links. Simply replace the -'s with .'s when you poaste in your browser and it should work)

www-support-microsoft-com/kb/208946


I had some trouble finding a copy of the example database but eventually found it online. I now have no idea where this was though!

Thanks for your time on this.

Steve