Ranking records in Parameterized Query

[DJ44]

Good day.

I have a developed a query with a field called MaxScores. I have another field which ranks the MaxScores 1 thru n based on the value in the scores. I use the following statement to rank the MaxScores:

Ranking: (SELECT Count(*) FROM [rqryMaxScores-8] Where [MaxScore]>[AliasMaxScores-8].[MaxScore])+1

My problem is this: The query is the recordsource for a form. When I open the form using the OpenForm method, I feed a parameter to the query in the Where argument that specifies the string Field Region in the same query.

When the query is executed, only the desired region appears (GOOD!) however the ranking is for all the regions. I want the rank to reflect the relative ranking for that region and not the whole project area.

To be more clear. The MaxScore for Site1 in Region1 is 101, which is Rank1 in Region1, but is Rank 4 when all regions are looked at. Now I get the value of 4 returned when I run the query, but I want it to return 1.

Any ideas??

All help is appreciated.

DJ

 

[DJ44]

Just another thought. The above query is based on another query (rqryMaxScores-8). When I (manually) use the Where criteria for the Region in that query and then run the Ranking query, presto! --the ranking field is relative to the region.

This brings up a new problem. How do I run two queries when I open a form that is based on the second query?

I have the form opening when I click a cmdButton on a switchboard. The code I have is as follows:

Private Sub cmdRankRegion1_Click()

On Error GoTo Err_cmdRankRegion1_Click
           
        
    
    stDocName = "afrmTest"
      
      
    
    
    DoCmd.OpenForm stDocName, , , "RegionID = 'Region1"
        
        
        
        
Exit_cmdBellPrior_Click:
    Exit Sub

Err_cmdRankRegion1_Click:
    MsgBox Err.Description
    Resume Exit_cmdRankRegion1_Click
    
End Sub

This will run the second query, which is the recordsource for the form. I need to pass the Where to the first query and then just open the form without a Where argument.

Help!

Thanks. DJ

 

[DJ44]

Woops, I re-read my last post and realized I missed a ' here and there.

Corrected code:

Private Sub cmdRankRegion1_Click()

On Error GoTo Err_cmdRankRegion1_Click
           
        
    
    stDocName = "afrmTest"
      
      
    
    
    DoCmd.OpenForm stDocName, , , "RegionID = 'Region1'"
        
        
        
        
Exit_cmdRankRegion1_Click:
    Exit Sub

Err_cmdRankRegion1_Click:
    MsgBox Err.Description
    Resume Exit_cmdRankRegion1_Click
    
End Sub

Thanks again.