Assign number 1-11 to fields in a table

[TOTCOM11]

I have a database which I am using to keep track of a fantasy football draft. I have 11 teams in the league thus far and would like to randomly assign each of these teams a "draft order" number. Each team must have a unique draft number and the numbers must be in the range of 1-11. I have a "Teams" table set up with the following fields:

Team As Text
DraftOrder As Integer

Is there some way I can use a query or VBA code to assign a unique draft number to each of the teams?

TOTCOM11

 

[TOTCOM11]

I currently have an update query that updates the "DraftOrder" field with values that come from a function called "DraftPickOrder()". This function takes whatever value is calculated by the random code and stores it in the variable until it is stored in the table via update query. My problem is I don't know how to write the code to output numbers 1-11 in a random order. Can I use an array to keep track of what numbers have been used already, and if so what would the code look like?

I have SOME code that allows me to calcualate the number:

DraftOrder = Int((11 - 1 + 1) * Rnd + 1)

I just need to know how to keep track of what values I generate so that I don't use the same number twice.

 

[GoDawgs]

I think I would just put the names in a hat and assign them that way.

I guess that's not what you're looking for. Hmmm...I think you need to change your thinking a bit, instead of randomly selecting a 1-11 number I would go the other way and randomly select the 11 people. Set up a loop that loops through eleven times (storing that as DraftOrder), then inside the loop have another loop that just uses the RND function to run through the numbers 1 through 11 (the IDs of the Draft participants) a random number of times (so it picks a random participant). Then say you end up with number 6 (the 6th participant...Joe Montana for example)...check and see if Joe already has a DraftOrder number assigned, if he doesn't then assign the DraftOrder variable to him and loop to the next DraftOrder variable, otherwise stay at the same DraftOrder variable and run through the loop with the RND function again to find another participant. Make sense? Hope that helps...

Kevin

 

[raskew]

Designate each element in the group with a letter, starting with 'A'. For the sake of a random group order, it doesn't matter how you elect to make the designation.

Copy/paste the following code to a new module. To call it, from the debug window:
(for a group of 11) ? randomstring2(11) <enter>
The randomly sorted results will look like:
I C E H J A G F K B D
9 3 5 8 10 1 7 6 11 2 4

In the above example, Team I would be first, Team C second, etc

Public Function RandomString2(pNum As Integer) As String

Dim i As Integer, x As Integer
Dim intNumber As Integer
Dim strValue As String, strHold As String

strValue = ""

For i = 1 To pNum
    Randomize
    Do
        intNumber = Int((pNum - 0 + 1) * Rnd + 0)
        strHold = Chr(intNumber + 64)
        'ensure that the sequence is not repeated nor = 0
    Loop While InStr(strValue, strHold) > 0 Or intNumber = 0
    strValue = strValue & strHold & " "
Next i
'display results as alpha-characters
Debug.Print strValue
strHold = ""
Do While Len(strValue) > 0
   strValue = Trim(strValue) + " "
   x = InStr(strValue, " ") + 1
   strHold = strHold & " " & Asc(Left(strValue, 1)) - 64
   strValue = Mid(strValue, x)
Loop
'return results as integers, 1 to pNum
RandomString2 = LTrim(strHold)

End Function

 

[TOTCOM11]

Reskew, that is some awesome code! However, it does not do what I want. The code you wrote puts 11 numbers ranging in value from 1-11 in a random order in a string. What I'd like is for the function to return 11 times, each time containing a unique number ranging from 1-11. I have an update query which includes RandomString2(11) in the update criteria. Right now the string is stored 11 times, once for each team in the table that I am updating. Is it possible to write this code so I can have it return 11 unique numbers, 1-11?

 

[raskew]

Should have explained it more completely.
For starters, add field [TeamID] (text, no duplicates) to your table.
Populate the field with letters &quot;A&quot; to &quot;K&quot; in whatever pattern you desire,
as long as there are no duplicates.

If the results of running RandomString2(11) are &quot;ICEHJAGFKBD&quot;, then we use the Instr() function to return the position of a particular team's TeamID in the string, and that becomes the DraftOrder. For example, TeamID &quot;A&quot; in the 6th position, so that team is assigned DraftOrder = 6.

Tweaked the function to just deal with letters and named it RandomString3().

The entire query to update the teams' DraftOrder would be:

UPDATE tblTeam2 SET tblTeam2.DraftOrder = InStr(RandomString3(11),[TeamID]);

The revised function looks like this:

Public Function RandomString3(pNum As Integer) As String
'*****************************************************
'Name:      RandomString3 (Function)
'Purpose:  Generate a random string of characters
'                 with no dups.
'Author:    raskew
'Inputs:    ? RandomString3(5)
'Output:    will vary each time it's run, e.g: DBCEA
'*****************************************************

Dim i As Integer, intNumber As Integer
Dim strValue As String, strHold As String

For i = 1 To pNum
    Randomize
    Do
       intNumber = Int((pNum - 0 + 1) * Rnd + 0)
       strHold = Chr(64 + intNumber)
      'ensure that the sequence is not repeated nor = 0
    Loop While InStr(strValue, strHold) > 0 Or intNumber = 0
    strValue = strValue & strHold
Next i

RandomString3 = strValue
End Function

Hope this makes more sense.

 

[MichaelRed]

I assume that an arlier post to this was lost (RF?).

The approach may be &quot;O.K.&quot; for small sets, but suffers severs penalties in that the testing for and repeated generatoin of the random value is the dominating factor in the procedure. It is far more efficient to assign a 'floating point' value to each element and then (efficiently!) sort the array according to the random value. Attempting this on a large set of values will dramatically slow the entire app (and -without some breaks in the execution (e.g. DoEvents)- the entire worlkstation.

raskew, refering to [RandomString3] in particular, your soloution does not consider the context of the thread. The procedure returns a (non-delimited-e.g. just the set of chars) string, while the question is clearly asking for a procedure which would be suitable for the assignment of [b[A[/b] value to a field in a query. Further, you do not state this or give even rudimentary guidance on how to seperate the individual values to specific entities in any ordered set. Since the thread originator is (obviously?) not at all equipped to deal with the somewhat simplistic process, I would guess (assume?) that they are equally ill equiped to acomplish the transform from the single &quot;word&quot; of n-characters to individual characters being assigned to individual records in a recordset.






MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[TOTCOM11]

Personally, then...I feel that MichaelRed should write some code in which HE feels would help my problem. The problem with raskew's code is that I already have a table with the team IDs already assigned...hence the name of the teams. All I would like to do is return values 1-11 in a random order to the &quot;DraftOrder&quot; field of my table. Isn't there an easy way to do this?

TOTCOM11

 

[GoDawgs]

Ok, well if MichaelRed is done with his belittling of everyone (I think at least, it's hard to read when every third word is misspelled or put in quotes for some reason). I think I have a solution for you TOTCOM11...I'm still a 97 guy (hard to believe) so it's DAO, so you may have to work with it a bit, but at least you can see how it's done:

Function OrderEm()

Dim RST As DAO.Recordset, TheOrder As Integer
Dim MoveOn As Boolean, TheID As Integer

Set RST = CurrentDb.OpenRecordset(&quot;Teams&quot;,dbOpenDynaset)
For TheOrder = 1 To 11
MoveOn = False
Do Until MoveOn
TheID = Int((11 * Rnd) + 1)

RST.FindFirst (&quot;[teamid] = &quot; & TheID)
If RST![draftorder] = 0 Then
RST.Edit
RST![draftorder] = TheOrder
RST.Update
MoveOn = True
End If

Loop
Next TheOrder

End Function


Basically it's just randomly selecting teams and if they don't already have a draftorder it's assigning the current draftorder in the loop. Hope that helps.

Kevin

 

[TOTCOM11]

Personally, I think all MichaelRed likes to do on here is talk like he knows something and offend those he's &quot;helping&quot; in the process. I guess it goes back to the rule, if you're not being helpful, get out of the way...

 

[TOTCOM11]

Thank you for the help, Kevin...but I need the function to return a value back to my query for this to work. In your case OrderEm needs to be equal to whatever it is that is being outputted...that way I can assign these numbers to my table. I really appreciate your effort in this! As for Michael...your services are no longer needed.

 

[GoDawgs]

I think you're going to have a hard time doing this in a query just because of the fact that you'd have to keep track of what numbers are being used, but you wouldn't really be able to do that since you are restarting the function each time...you'd have to loop through and see which numbers have already been used each time the function started I think. Any reason you can't just run the query without that function and then afterwards run the function I gave you? It could all be done in code and the users wouldn't know the difference.

 

[raskew]

In responding, I envisioned your table as having eleven records, one for each team. In the initial post you described the table has having two fields: Team (which contains the team's name) and DraftOrder. My proposal was/is to add an additional field, TeamID, which will be populated with a randomly selected letter between A and K. This field has no real relation to the team's name and is not intended to replace the name. It's just a mechanism to aid in the random selection process.

In laters posts you specified that DraftOrder must be a unique number. In my mind that meant that no number would be used more than once (e.g., there could be no two teams with DraftOrder = 3). With this is mind, the randoming process need only to be run once, producting an eleven-character string of randomly positioned letters between A and K. The update query described in my previous post uses the InStr() function to return the position of field TeamID's contents against the generated string and this integer (1-11) becomes the record's DraftOrder. I have built a model as described and it works well.

If the operating assumptions were off-base, please clarify.

Best wishes,

Bob

 

[TOTCOM11]

Bob,

Now that you explained your logic, it makes a lot of sense of what you were trying to accomplish. My only question then is how do I get the TeamID &quot;letters&quot; into the field TeamID? I've just started learning about how I can use functions to return values to a query.


Go Dawgs,

I'm not sure what you mean by running the query first and then the code. The update query without the code does nothing. My tables are already in place. The only thing missing is values in the DraftOrder field.

Does this help answer my question?

 

[GoDawgs]

My fault, I thought the query was doing something else. Well if the table is set and ready to go then just copy the function into a new module, make sure that each of the DraftOrder fields are numbers and are currently zero in your table, then just press F5 on the &quot;Function&quot; line in the code...then you're done.

 

[TOTCOM11]

When I try running the code, I get an error on the following line:

Dim RST As DAO.Recordset, TheOrder As Integer

I am currently running Access 2002. Does this make a difference?

 

[GoDawgs]

Yeah, like I said, I'm still in 97, 97 uses DAO while the newer versions use ADO. I'm not sure if you'll have this, but there's a DAO reference that you can add in that will make it understand what DAO is...although if you have no programming experience it might be hard to find. When you have a module open click on Tools, then References. You need scroll down and check these two:

Microsoft DAO 3.6 Object Library
Microsoft Visual Basic for Applications Extensibility 5.3

If you have both of those then it will work...if not, you're probably out of luck. Like I said, this was just code to get you going in the right direction.

 

[TOTCOM11]

OK, I got it to run now. But I'm getting a runtime error:
&quot;Data type mismatch in criteria expression&quot;

The debugger points to the following code:
RST.FindFirst (&quot;[TeamID] = &quot; & TheID)

What's going on?

 

[GoDawgs]

Is the TeamID field in your table a number or text field? I assumed that it was a number field numbering the teams from 1-11.

 

[TOTCOM11]

Now the code just keeps running in the loop. The loop won't end.