Making a ranking table

[gblack]

OK, I have been using MS Access for quite some time now (since Access 2.0), but I have never gotten a handle on VBA. I use the event procedures and such, behind forms and copy code from Macros that do what I want (mostly). But I don’t really understand those three page long blurbs of code (that people frequently post on these boards), or where I should put them, or how to make them run.

So, that said maybe I am too much of a novice at VBA, but I would like to know how to do something if anyone is willing to tell me EXACTLY what to do. Here goes:

I have a table (say table name is "Problem Count"). The table has four fields. The Field names are ID (PK), Region (this is a text field representing the region), Problem (which is the problem text field that I wish to rank), Count (which is a count of the problem).

What I wish to do is make a new table based on the previous one, which lists each region (ten times) and the top ten problems within each region and the counts of each problem. So the table should look something like:

Region Problem Count
…
West Coast problem-A 250
West Coast problem-C 247
West Coast problem-D 222
West Coast problem-X 127
West Coast problem-M 125
West Coast problem-T 100
West Coast problem-Y 50
West Coast problem-B 45
West Coast problem-E 23
West Coast problem-O 1
Western Problem-D . . .

There’s a kicker. . .some of the regions don’t have 10 problems they may have 9 or 8 total. . .but others may have 35 and such. In the case of less than 10 (say 9), I want the region to appear in a 10th record (for said region) and have a null value appear in the problem field and Zero appear in the count field.

Also once the code is written. . .where do I put it? I'm assuming it goes in the Module section, but do I simply cut and paste it there, or is there something special that you need to do to make it create the table. . .

If anyone wishes to attempt to help the helpless . . .thank you in advance

Gary

 

[yeatmanj]

This can be done through a series of access queries that run on click on other event.

A sum or count query, A query with the formula that creates the 10th record with the null value (or run after make table query as an append query), A union query to combine the tenth value, and a make table query to put all the information into place.

I believe you would only have to run the make table query from whatever event to complete the entire transaction. Now, this is the least technical method I know to do what you are asking.

I'd love to give you more exact details, but without actually creating the thing I'm just not that good. This method would work for you, but it will be a fair bit slower than doing the process through SQL statements. You can however create the whole process in Access queries then change to SQL view for each query and copy the text. You would then paste into either the form module or a module with a public procedure that you will be calling from a form module.

To execute the SQL statement use DoCmd.RunSQL. Here is an example.

DoCmd.RunSQL "INSERT INTO atblSite ( FacID, StaffID, DistrictID, ContractorID, ContractorName, " _
& "SiteName, SiteAddress, SiteCity, Code, Score, OwnerTitle, OwnerFirstName, OwnerLastName, OwnerCompany, " _
& "OwnerAddress, OwnerCity, StateID, OwnerZip, OwnerPhone, OwnerExt, OwnerFax, DateAssigned, Status, Inactive, " _
& "Team6, UserCreated, DateCreated, UserEdited, DateEdited ) " _
& "SELECT tblSite.FacID, tblSite.StaffID, tblSite.DistrictID, tblSite.ContractorID, tblSite.ContractorName, " _
& "tblSite.SiteName, tblSite.SiteAddress, tblSite.SiteCity, tblSite.Code, tblSite.Score, " _
& "tblSite.OwnerTitle, tblSite.OwnerFirstName, tblSite.OwnerLastName, tblSite.OwnerCompany, tblSite.OwnerAddress, " _
& "tblSite.OwnerCity, tblSite.StateID, tblSite.OwnerZip, tblSite.OwnerPhone, tblSite.OwnerExt, tblSite.OwnerFax, " _
& "tblSite.DateAssigned, tblSite.Status, tblSite.Inactive, tblSite.Team6, tblSite.UserCreated, tblSite.DateCreated, " _
& "tblSite.UserEdited, tblSite.DateEdited " _
& "FROM tblSite WHERE tblSite.FacID = '" & strFacIDArchive & "';"

You have to make sure that you put the statement in quotes and do line breaks at the end (sqlstatement" & _) and begin the next line & "<sqlstatment>. You will also have to change some of the " that the Access query builder puts in to be ' to keep the syntax flowing.

I hope this is somewhat helpful. If you need any information you can find the SQL under SQL statements.

 

[gblack]

OK I'm lost

I don't see how this applies to my question? Maybe I'm not explaining correctly.

I don't want to add a bunch of fields to a new table,

I simply wish to have a table with 3 fields (Region, Problem, Count) for which there are ten records for each Region. These records should correspond to the top ten problems within the regions.

I'm not getting how (in the previous post) all those fields equate to this. Or am I missing something? Likely I am. . .

 

[yeatmanj]

That was simply intended as an example of what an sql statement should look like when run from VBA.

You can create the table before hand with the fields that you want and in place of a make table query you can use an append query to update the data based on the event that you have it run from.

You make one query in design view and activate the E (equation symbol) on the toolbar. This will give you a group by field that is a pulldown for sum, count, add, subtract etc. You populate the design view with the fields "Region" and "Problem" from your "Problem Count" table. Under the "Region" field you change group by to count. When you execute the query it will show you a total for each problem based on region. Then you can use an append query to enter the data generated into the table where you want to store the counts. You will have to use a combination of queries and work with it a bit to get the exact results you want, but in the end you should at max have to run two queries (append and update) from an event in your form module.

I don't really know any other way to do what you want without actually writing the application myself.

 

[gblack]

This is what I was looking for. . .

My friend helped me do this. I put this code in the on click event procedure of a button. If anyone cares, here's how I did it:

I had a table called [june top 10] which is really a bad name for it since it was not the top ten, but a List of Regions and a count of all the problems within the regions. It has three fields Region, Problem and Count.

I ended up having to create my destination table first and called it [top ten list] and has 4 fields Region (text), Problem (text), Count (number), and Rank (number).

Here's how it gets filled, It works great:

Private Sub Command0_Click()
Dim DBCur As DAO.Database, Regionlisttable As DAO.Recordset
Dim origtable As DAO.Recordset, SQLString As String, i As Integer, desttable As DAO.Recordset
Dim curregion As String

Set DBCur = CurrentDb()


SQLString = "SELECT distinct(region) FROM [june top 10]"
Set Regionlisttable = DBCur.OpenRecordset(SQLString)

SQLString = "SELECT * FROM [top ten list]"
Set desttable = DBCur.OpenRecordset(SQLString)

While Regionlisttable.EOF <> True
curregion = Regionlisttable.Fields("region")
SQLString = "SELECT * FROM [june top 10] where region = '" & curregion & "' order by total desc ;"
Set origtable = DBCur.OpenRecordset(SQLString)
origtable.MoveFirst

For i = 1 To 10
desttable.AddNew
If origtable.EOF = False Then
desttable.Fields(0).Value = origtable.Fields(0).Value
desttable.Fields(1).Value = origtable.Fields(1).Value
desttable.Fields(2).Value = origtable.Fields(2).Value
desttable.Update
origtable.MoveNext
Else
desttable.Fields(0).Value = curregion
desttable.Fields(1).Value = "No " & i & "th Problem"
desttable.Fields(2).Value = 0
desttable.Update
End If
Next
Regionlisttable.MoveNext
Wend


origtable.Close
Set origtable = Nothing
desttable.Close
Set desttable = Nothing
Regionlisttable.Close
Set Regionlisttable = Nothing
DBCur.Close
Set DBCur = Nothing

End Sub

 

[yeatmanj]

That looks like an excellent solution. Just one suggestion though, you should stop naming things with spaces. If you have to put in the appearance of a space for yourself, then use an _ instead. This will prevent you from having to deal with serious syntax issues in other places.

I'm sorry I wasn't any help. I tried!

 

[gblack]

Thanks!

Thanks for the tip. . .as an old GUI user, its hard for me to think in those terms. But I think you are right about the spaces. . .

Thanks for the help.

 

[MikeAngelastro]

Hi,

I read the "solution" and immediately thought that the real problem is putting the right data into the "rank" field. I have solved that problem and it does not require a cursor or a table ( unless, of course, you want one just because you want one ). Here is what is needed to rank states by sales:

qryTestRankFunction = "SELECT State, Sales, Rank([Sales]) as Rank FROM SalesRecordset ORDER BY DESC Sales;

Public InitRank As Boolean

Public Function Rank(Optional Amount As Double) As Long
Static Counter As Long
If InitRank Then
Counter = 0
InitRank = False
End If
Counter = Counter + 1
Rank = Counter
End Function

On the click event of a command button, put the following code:

Private Sub btnRank_Click()
InitRank = True
DoCmd.OpenQuery "qryTestRankFunction"
End Sub

The result will be a recordset with the proper ranking in the "Rank" field. This recordset can be used for whatever you want.

The important thing to remember is to initialize the rankings to ensure the static variable starts at 1.

Mike

 

[gblack]

Neat . . .I'll try it!