weighted numbers macro

[TrackStar]

I have made a database in access to randomly pick numbers. I just need to weight the numbers based on type. After a lot of research, I feel I need to make a macro that weights product number types. Some products would be A, B, C and D and A would weighted at a high percentage of being picked at 90% for example and the other types would be picked less likely. Maybe B types would be picked 70% of time and C and D types around 30%. Can anyone help guide me on coding for this?

[John_G]

Hi -

One way you might approach this is to use the random number to get an initial value, then use if-then-else or select case to determine the range the random number is. Something like this :

dim X as double
X=rnd()
select case X
case is <= 0.6
debug.print "A"
case is <= 0.8
debug.print "B"
case is <= .95
debug.print "C"
case else
debug.print "D"
end select

What that does is divide the reults into 4 uneven sections. A = 60%, .... D = 5%, because rnd returns 0 <= X < 1.0

HTH

John

[TrackStar]

I am still a little confused. The cases make sense, but where do I put "dim X as double
X=rnd()". Does this go into a module?

Here is a little bit of the data I am working with:

ID, Product Numberm, P/N Class
1, jjnjd101-20, A
2, hhhjj99-10, A
3, rrrtty-001, A
4, reeed-00, B
5, tr-2232-10, B

I used Rnd(Abs([ID])) in my query so ID = X. Does this make sense? I'm using access 2010 by the way.

[John_G]

I don't quite see what it is you are trying to do. If you pick records in your table at random (without weighting) , then the frequency Class "A" is chosen (for example) will depend only on the frequency Class "A" appears in the table. So, for example, if 50% of the records in you table were Class "A", then you would expect Class "A" to be randomly selected 50% of the time.

Why are you putting Abs([ID]) as a parameter in the rnd function? Can you clarify, please?

John

[TrackStar]

If I do not put Abs or use nz in that spot of the code, then I would not continuously have different sets of random numbers. In my case, I want 5 random numbers. I would get the same random numbers to appear if I did not use abs/nz. I want to print out a different set of numbers each time.

The data in my data is roughly 50% A's, 40% C's and 10% made up of B's and D's. I do not want many C's to randomly show up when I print because they are not as important. I would like to make A's and B's weighted to appear at a higher percent without changing the table contents aka creating a macro or module that weights number types.

[John_G]

With a little help from other newgroup postings here:

http://www.accessmonster.com/Uwe/For...-using-a-query

and here:

http://www.accessmonster.com/Uwe/For...random-records

you can try something like this:

1) Write a query based on your table (include all fields) , but with an additional random number field using the ID as the Rnd parameter - have the query sort on that field (when you run the query, it won't look sorted - I tried it!). Suppose you call it Randomquery.

2) Modify the code I showed you above to return A, B, C or D in approximately the proportions you need

3) In VBA, create a recordset to retrieve the first record from that new query where the Class matches the one determined above:
Select Top 1 * from RandomQuery


In pseudo-code, something like this:

Loop for 1 to 5
select a class_ID using weighted random numbers
Select top 1 record from RandomQuery
Print results
end loop

You could put all that in one VBA procedure.

Give it a try and let us know how it works.

John

[TrackStar]

For step 1, you add an additional random number field as a Rnd parameter? Where and how am I creating this parameter? From the parameter button?

For step 3, what do you mean by recordset? I don't understand how you are matching Classes to the code above.

[John_G]

Hi -

The additional field you are creating in the query should look something like this:

rnd([ID]), where [ID] is the ID field in your table.

For your second question - I assumed that you have a reasonable knowledge of VBA; was I wrong?
It would take a lot of space (and time) to explain what recordsets are, how they work, and how to use them. I suggest (gently) that you find some good tutorials on VBA and learn how to use it to your advantage. Here are a few:

http://www.functionx.com/vbaccess/index.htm

http://allenbrowne.com/tips.html

There are dozens of others (not all free). Google access vba tutorial.

Since you are getting into some data manipulation which is beyond "simple", you should take the time to properly understand VBA.

John

[TrackStar]

John

I understand the rnd(ID) parameter. I have no VBA experience really.

I just don't know where to put the A,B,C,D weighted code unless the module is right.

I understand part more of what a recordset is now, thank you. What should be the right code a recordset in a query? I don't think I should use private sub xxxxxxxxxx.

Here is my code in my module:

Option Compare Database
Public Function GetRandomValue(fld As Double)
Randomize
GetRandomValue = Rnd(1)
End Function


Select Case GetRandomValue
Case 1
Case Is <= 0.9
Debug.Print "A"
Case 2
Case Is <= 0.7
Debug.Print "B"
Case 3
Case Is <= 0.3
Debug.Print "C"
Case 4
Case Is <= 0.25
Debug.Print "D"

End Select

[John_G]

Hi -

First, modify the GetRandomVariable function to return A, B, C or D randomly, but weighted:
It doesn't need a parameter.

Public Function GetRandomValue() as string
dim RandomValue as double
Randomize
RandomValue = Rnd(1)
Select Case RandomValue
Case Is <= 0.9
GetRandomValue = "A"
Case Is <= 0.7
GetRandomValue = "B"
Case Is <= 0.3
GetRandomValue = "C"
Case Is <= 0.25
GetRandomValue = "D"
End Select

End Function

But you don't have the logic right in the Select Case block. A Select Case will execute the first condition that applies, and ONLY the first. As you have it shown, if RandomValue is <= 0.9, you will always get "A", and only "A" - never "B", "C" or "D". If Randomvalue is > 0.9, you won't get anything, because no conditions are satisfied.

Here's how you might use that in a procedure; I make the assumption that you have a query called Query1 that randomizes the order of records in the table, and that it only returns 1 record.

Sub RandomPrint
Dim J as Integer ' Loop Counter
Dim db as Database
Dim rst as Recordset
Dim SearchClass as String
Dim SQL as string ' Used for the SQL that creates the recordset

set db = CurrentDB

For J = 1 to 5
'
' Get a random class code
'
Searchclass = getRandomValue()
'
' Open the recordset, using only parts of the class, but randomly selecting which
' one of those you get.
'
SQL = "Select * from Query1 where [P/N Class] = '" & SearchClass & "'"
set rst = db.OpenRecordset(SQL)
'
' Print the data you need
'
debug.print rst!ID, rst![Product Numberm], rst![P/N Class]

next J ' end of the loop

set db = nothing

end sub

Were you able to design a query with the random feature in it? Note that queries do not contain recordsets, but you can define a recordset based on a query, as I did. You can only work with recordsets in code modules.

The SQL for your query1 might look something like this. MS Access

SELECT TOP 1 Rnd([id]) AS Expr1, *
FROM yourtable
ORDER BY Rnd([ID]);

What you are doing in the SQL = ... statement is writing a query based on another query, instead of a table.

HTH a lot more

John

[TrackStar]

John


Here's my database in a zip. I feel like I am missing a connection to printing because the results look the same as before. When I say the same I mean random numbers but not weighted. I like the query I made is fine.

If you get a chance to look at what I have put together let me know if you see a solution. I want the final product(report) to have 3 groups of 5 random numbers on a page. Each group of 5 is separated by a dotted line. I only changed the the random code query so far to show you the Top 1 query. Would I have to put the new query that contains Top 1 randomized results in another query or is their another method to pull five numbers for the report that are random and weighted?

[John_G]

Are you able to save your database as an .mdb and re attach it? I don't have A2007/2010 yet (real soon now, they tell us.....)

Would I have to put the new query that contains Top 1 randomized results in another query or is their another method to pull five numbers for the report that are random and weighted?

The difficulty is that the weighting you are applying to the A,B... classes is not (necessarily) the same as the distribution of those classes in the table, so you cannot just randomly select from the table. The only way I can see is to randomly, (but weighted) choose A, B, C or D, then select one record, again randomly, from the table where the class matches.

This could lead to what appear to be strange results; for example if you give Class "D" a high weighting (importance), but there are not very many Class D's in your table, then the chances of a record being selected more than once could be relatively high.

So, if you can post your DB as an MDB, I'll take a look at it.

John

[TrackStar]

John

I converted my database to 2000 and a 2002-2003 format. Thank you so much for looking.



Mike