Rank / Position query

[shark70]

Hi

I am trying to set up a football predictor database but am having problems with the Position column in the League Tables.

I have the Points, Goal Difference, Goals For etc., OK but I don't know how to add the Position/Rank column as the 2nd table below shows by Points then Goal Difference, then Goals For etc.,

Can anyone help please?

Team Points Goal Diff Goals For Goals Against
Arsenal 12 11 16 5
Chelsea 12 5 6 1
Bolton 9 3 7 4
Tottenham 8 2 4 2
Middlesbro 7 1 9 8

Position Team Points Goal Diff Goals For Goals Against
1 Arsenal 12 11 16 5
2 Chelsea 12 5 6 1
3 Bolton 9 3 7 4
4 Tottenham 8 2 4 2
5 Middlesbro 7 1 9 8

Thanks
Shark70

 

[fernin8r]

On your query statement at the ORDER BY at the end of it.


SELECT ... ORDER BY Points, Goal Difference, Goals

is this what your asking?

 

[shark70]

No, I have the query in the correct order but I need to add another column to show the position of each team according to Points, Goal Difference, Goals For etc.,

Cheers
Shark70

 

[Jon K]

The easiest way is to show the Positions in a report. All you need is add a text box in the report and set two properties:-
Control Source =1
Running Sum Over All


If you need to show the Positions in a query, you can change your query into a make table query to create a temporary table. Then use VBA code to add an AutoNumber field in the temporary table.

As Access will add the AutoNumber field at the end of the field list in the temporary table, you can use another query to show the fields in their proper order.

I have attached a sample database. You can open the form and click on the command button to create a temporary table and run a query to view the records. The code used is in the On Click Event of the command button:-

Private Sub cmdCreate_Click()
    
   On Error Resume Next
   DoCmd.DeleteObject acTable, "tblTemp"
   On Error GoTo 0
   
   DoCmd.SetWarnings False
   DoCmd.OpenQuery "Query1 make table"
   DoCmd.RunSQL "ALTER TABLE [tblTemp] ADD COLUMN [Position] AutoIncrement;"
   DoCmd.SetWarnings True
   
   DoCmd.OpenQuery "Query2 view table"
   
End Sub

 

[shark70]

Rank

Excellent, cheers
I needed it as a query so the 2nd option works
Don't know much about VB yet but plan on learning it

Thanks again

Shark70