Randomizing a Resultset 2

[ajetrumpet]

This a continuation of my first one guys...

I am stuck on one last issue:

I have a "SELECT TOP n" SQL statement that applies to a query. I want to randomize the set, but by using an array. I can use GetRows to populate the 2-dimensional from a recordset opened by the statement, and then create a table from that shuffled array, but I don't want to create a table. I want to create a query. How in the world do I create a query using .CreateQueryDef if the method uses an SQL statement, and all I have is a single array with a bunch of values in it?

And Pono, if you're looking at this and thinking I'm am repeating myself, that's because I am, for the first time in a LONG time, confused as he**!!

 

[pono1]

Confusion is my middle name...sorry if I lead you astray... And I know this might be annoying to read but could you please tell us again what you hope to do...and (maybe more annoying) why you're doing this?. Is this is a how-to challenge or will this be something that plugs into an app you're building or something else?

 

[ajetrumpet]

Confusion is my middle name...sorry if I lead you astray... And I know this might be annoying to read but could you please tell us again what you hope to do...and (maybe more annoying) why you're doing this?. Is this is a how-to challenge or will this be something that plugs into an app you're building or something else?

I knew you would respond to this.

And yes, it will probably go into an app in the future. My father also programs in Access, and markets custom software. So, it will most likely be used for that at some point.

My objective: Select a table from a combo, then select desired fields (of that table) in a multi-select listbox, and then type in the number of records to return. The result will be a pop-up query that contains that number of records, in random order.

I have the controls, and the query to be created, coded, all in the module. I have decided that I have to randomize values using an array (per your thoughts) and the Randomize statement.

The facts:
**The array gets rows from the SQL.
**The SQL is created from the user specified table name and field names.

Question: After I get the rows (from the SQL recordset) into the array and shuffle them with the randomize statement, how do I get them back out of the array and into the SQL statement, so I can create the query from it?

Until this is put to use, it is a How-to Challenge. You should know by now, that I am the master of these types of things.

 

[pono1]

My objective: Select a table from a combo, then select desired fields (of that table) in a multi-select listbox, and then type in the number of records to return. The result will be a pop-up query that contains that number of records, in random order.

Mmm. One tough part: the result has to be pop up query (like DoCmd.OpenQuery?)...

If you only needed to randomize the results for the user's benefit, you could just...

. get all your records using a SELECT statement

. throw the records into an array

. randomly get X number of items from the array

. put the randomly chosen items into a listbox (or something)

. display the listbox (or something) to the user

The poor thing about the above...you could be potentially bringing over tons of records when you only need, say, 6. This is where a stored procedure or two would be handy since you could do all the logic on the server...

Half a cup of coffee thought...

Regards,
Tim

 

[ajetrumpet]

If you only needed to randomize the results for the user's benefit, you could just...

. get all your records using a SELECT statement

. throw the records into an array

. randomly get X number of items from the array

. put the randomly chosen items into a listbox (or something)

. display the listbox (or something) to the user

Tim, none of the stuff you mentioned is relevant right now. I already have the process set up, per my last post:

I have the controls, and the query to be created, coded, all in the module. I have decided that I have to randomize values using an array (per your thoughts) and the Randomize statement.

The above quote is referencing this quote:

Originally Posted by ajetrumpet
My objective: Select a table from a combo, then select desired fields (of that table) in a multi-select listbox, and then type in the number of records to return. The result will be a pop-up query that contains that number of records, in random order.

Do you see what I mean? This has already been done. I have 3 criteria (as per my quote) for input. After those are entered, the query will pop up. Right now it is a .CreateQueryDef, not a DoCmd. How could I possibly open the query with a command statement? To do that, the query would already have to be created. I have to make it first! And since this is completely done in the module, I can't be working with the grid.

This might be easier if I just upload the sample for you. I will do that when I get home tonight.

 

[pono1]

You're one tenacious project manager... I think I get it--An interesting exercise but I'm too tired to think hard on this...will try to check back by the weekend at which time you will probably have the thing figured out...

Regards,
Tim

 

[WayneRyan]

Adam,

You still have to write the query, but you can make it easier.
Add an Order By clause to generate a random number.

Get the "TOP 3" part from your TextBox.
Get the "Field1, Field2 ..." from your ListBox.

SELECT TOP 3 Field1, Field2 ... Fieldn 
FROM tblAccountDetails
ORDER BY GetRand([AccountID]);

Public Function GetRand(SomeSeed As Long) As Double
  Randomize (SomeSeed & CLng(Now()))
  GetRand = Rnd()
End Function

hth,
Wayne

 

[ajetrumpet]

Adam,

You still have to write the query, but you can make it easier.
Add an Order By clause to generate a random number.

Get the "TOP 3" part from your TextBox.
Get the "Field1, Field2 ..." from your ListBox.

SELECT TOP 3 Field1, Field2 ... Fieldn 
FROM tblAccountDetails
ORDER BY GetRand([AccountID]);

Public Function GetRand(SomeSeed As Long) As Double
  Randomize (SomeSeed & CLng(Now()))
  GetRand = Rnd()
End Function

hth,
Wayne

Wayne,

Your post is wonderful, but it addresses the same problem (I think) that I have been dealing with all along: The damn Rnd() function! It only accepts numeric values! So, the question is: How can I use that function to do anything at all (in terms of randomizing) if every field's data type in the table that is chosen is, for example, a STRING (or any other non-numeric)??? If you want to look at the code, the relevant portion is in the last thread I started on this. Found here.

I have finally attached the sample (in A2002-03 format). This works absolutely perfect if there is a numeric data type in the table that is chosen. Actually, the field that is inserted into the Rnd() function (through the coding) is set as the first field from the top of the listbox that is selected. But, that can be easily changed to search the recordset for a numeric data type field.

Pono, as I said before, I think the array shuffling is the only way to fix this. I now have the randomizing code for this method, but I still don't know what to do with the array elements after I shuffle them! The reason I don't know is because I want to keep the process that I have right now.

One more thing: I could always shuffle the array, populate a new table with its records, pop open a query on that table, and then delete the table. To a user, that method would not change anything (as far as the process), everything would still work as it does in the sample, and the problem with numerics would be solved! But as for me, I say it sucks because the programming process would have lost its elegance.

 

[WayneRyan]

Adam,

Oh, I hadn't seen your initial post ... very similar I'd say.

Your --> Order By Rnd([Field1])

Has a flaw in that if run daily, for example, it will return the
same result set every day!

You need to use the Randomize function to get truly random numbers.

To get a unique value, use --> Order By GetRand([Field1])

You aren't gonna do anything with [Field1], it just invokes the function
for each row of the table.

Public Function GetRand(SomeField As Variant) As Double
  Randomize ()    <-- Will use system timer as a seed
  GetRand = Rnd() <-- Will give a random number
End Function

hth,
Wayne

 

[ajetrumpet]

Public Function GetRand(SomeField As Variant) As Double
  Randomize ()    <-- Will use system timer as a seed
 [B][COLOR="Red"] GetRand = Rnd() <-- Will give a random number[/COLOR][/B]
End Function

Wayne,

Look at the above code in red. Now, consider this scenario:

**one table = Table1
**3 fields = Field1 (string data type), Field2 (string data type), Field3 (string data type)

Now, please tell me how to make your function work with Field1 as the input:

Public Function GetRand([B][U][color=red]Field1[/color][/U][/B] As Variant) As Double
  Randomize ()    <-- Will use system timer as a seed
  GetRand = Rnd() <-- Will give a random number
End Function

Tell me how to work that, consider the following facts from the Access help menu:

Syntax= Rnd[(number)]

The optional number argument is a Single or any valid numeric expression​

.​

Does the output of "Double" and the input of "Variant" solve the conflict that you see regarding numeric values from the help menu snippet?

As for this quote of yours:

Your --> Order By Rnd([Field1])

Has a flaw in that if run daily, for example, it will return the
same result set every day!

I have NO idea what you're talking about. My sample has nothing to do with "daily" anything...

 

[ajetrumpet]

Wayne,

I am going to implement the method that I described in the last paragraph of post number 8. If you're interested, I will post a copy of this when I am finished.

 

[WayneRyan]

Adam,

You're trying to generate a random number, it doesn't matter what
the datatypes are for the columns in your table. Yes, the optional
argument for the Rnd function is numeric, but you really don't want
to feed it the same # for each row every time. Otherwise the next
user will get the same result set as the previous user.

Just trying to avoid the situation where each user's get the same
result everytime.

Help for Randomize:

Because the number argument has been omitted, Randomize uses the return
value from the Timer function as the new seed value (i.e. unique).

Help for Rnd():

Not supplied The next random number in the sequence.
Greater than zero The next random number in the sequence.

You will ALWAYS start with the same sequence.

For a Random number, you really don't need to supply any arguments.
The only reason [Field1] is passed as an argument to the function is
to force a value for EVERY row. Otherwise all of your random numbers
are the same.

Will be looking forward to your example.

Wayne

 

[ajetrumpet]

Shuffle Example

Wayne,

Attached is the array shuffling code I plan to use (file is in A2002-03 format). What do you think of it?

To see the results, go into the module, run it, and view it in the immediate window.

This is the only way I can think of to get around the limitations of the Rnd() function. By using this shuffle code, data types are no longer an issue when randomizing the record order.

There might also be a problem with this though, as I have noticed a few repeating "shuffled" orders of the recordset. There really shouldn't be, as the new elements of the array are randomly chosen from the start of the integer loop with the Randomizer statement (Rnd * rs.recordcount). It makes me wonder if there is some sort of limitation on this method as well. If there is, I'm not sure I can think hard enough to spot it!

The variables being thrown around and looped through here is enough to make anybody go mad! ​

Please tell me what you think, because I am seriously not following your posts on the Rnd() function. Thanks!

 

[WayneRyan]

Adam,

The code looks pretty good.

But, you need to call the Randomize function (with no arguments) at the
start of your code.

Without it, you sequences will be anything but random. Your first five
will always be:

7, person2, accessories, battery, 5, 202, HTH
18, person2, peripherals, mouse, 8, 40, CRST
16, person3, hardware, videocard, 3, 50.39, HTH
4, person2, hardware, computer, 18, 522.3, CRST
5, person3, electronics, radio, 10, 80.3, CRST

All I'm saying is that if the data were being "spot checked" for QA
purposes, you'd pull the same 5 items every day.

hth,
Wayne

 

[ajetrumpet]

Without it, you sequences will be anything but random. Your first five
will always be:

7, person2, accessories, battery, 5, 202, HTH
18, person2, peripherals, mouse, 8, 40, CRST
16, person3, hardware, videocard, 3, 50.39, HTH
4, person2, hardware, computer, 18, 522.3, CRST
5, person3, electronics, radio, 10, 80.3, CRST

Why would the first 5 always be those? I ran the function like 10 times, and I never saw those 5 records pop up in that order twice.

Which section of the code does this refer to?

 

[WayneRyan]

Adam,

Any time you START you app, you'll get the same "pre-determined" sequence
of random numbers unless you reseed with the Randomize function call.

If you got a new "random" list every day, it'd be the same "random"
records.

Start app,
Generate List - Observe values
Close app

Start app,
Generate List - Observe SAME values
Close app

Wayne

 

[ajetrumpet]

Adam,

Any time you START you app, you'll get the same "pre-determined" sequence
of random numbers unless you reseed with the Randomize function call.

You're right Wayne. I get different resultsets back after the each shuffle, but the order is not different everytime. There seems to be some sort of a loop going on with about 10 different ordered datasets (possible ordered outcomes). Like I said before, I really don't understand why this is. Remember the first line of code that initiates the randomizing of the integer variables? It is:

    int1 = Int(Rnd * rs.RecordCount)
int2 = Int(Rnd * rs.RecordCount)

I put a debug.print statement in the code after the "While Loop", like this:

int1 = Int(Rnd * rs.RecordCount)
int2 = Int(Rnd * rs.RecordCount)

      While int1 = int2
        int2 = Int(Rnd * rs.RecordCount) 
      Wend

Debug.Print int1 & ", " & int2

This obviously guarantees int1 <> Int2 for every loop cycle, but I'm having a hard time understanding why this is needed, and more importantly, its logic in the big picture. The printed list shows me duplicates being produced throughout the entire loop for each variable (int1 & int2). Now, how does that end up giving me unique elements of the array?

And lastly, I am not following you regarding the "seed" concept. I understand what a seed is, and I will reset it if necessary, but I also thought that int = Rnd * rs.recordcount would produce a truly random number everytime. If it does, then what part of the code is causing the array order to be duplicated every 10 shuffles? Or, is that too difficult to explain in words? And BTW, you've been a great help with this. I guess I can take you off my "do not call" list.

 

[WayneRyan]

Adam,

Couldn't we just do recursion instead?

Just kidding.

I hate stats and don't really use random #'s in general, BUT the "random"
numbers generated are not "random". But they're as predictable as your
7. 18, 16, 4, 5 sequence. It will happen anytime you start your app.

The randomize function will give you a "new" pointer into the "pre-ordained"
sequence. Without it you'll get predictable results.

The arguments for Rnd are:

Less than zero The same number every time, using number as the seed.
Greater than zero The next random number in the sequence.
Equal to zero The most recently generated number.
Not supplied The next random number in the sequence.

I'm just saying that regardless of these "options", you're gonna get some
number in the "pre-determined" sequence. It's always the same sequence,
UNLESS you use the Randomize function. That's why you need the function
call in the query. It didn't really matter what datatypes the fields were, they
could have been all nulls!

Doc, JON, PAT, Tim, Bob, Moniker ... Help!

Wayne

 

[ajetrumpet]

Wayne,

We've hashed through this long enough now, and I am not following you as much as I need to. You have the code that I have written, so how about you give me a snippet of your own code to fix this "seed" problem? Surely it doesn't take that much time to write something like that.

An example, using the data from my code, would be very helpful. Your post #7 is indeed helpful, but I can't do it because none of my data is there! Give me a little bit more to work with man...

If I can implement the shuffle code that I found on this website (which I did), surely I can follow anything that you write (especially if my own data is in it!). You have no idea how long it took me just to figure out what was happening with each line of code from that site!

 

[ajetrumpet]

Wayne,

I might have found what you're referring to. Check out this website:
http://www.freevbcode.com/ShowCode.asp?ID=3036

I have run it in Access, and I am now going to try it with a two-dimensional. I assume this is the kind of stuff I need?