Random Selection From A DB List

[MoBetter]

Does anyone know how to select a random record from a list of numbers already in a table (via Microsoft Access)?

If there is a list of numbers:
CustomerID
1
2
3
4

and I wanted to assign a specific award:
AwardNumber
100
200
300
400

to a customer (i.e. CustomerID 3 will recieve AwardNumber 200), how do I get the db to randomly select AwardNumber for each CustomerID without duplicating the same AwardNumber or vice versa?

 

[Remou]

Is a solution using VBA acceptable?

Sub Award()
Dim rs As DAO.Recordset
Dim i As Long

strSQL = "SELECT Rnd([CustomerID]) AS RandCust, t.Award " _
       & "FROM tblCustomer t " _
       & "ORDER BY Rnd([CustomerID])"
       
Set rs = CurrentDb.OpenRecordset(strSQL)

Do While Not rs.EOF()
    i = i + 1
    rs.Edit
    rs!Award = i
    rs.Update

    rs.MoveNext
Loop

End Sub

http://lessthandot.com

 

[PHV]

Remou, how is your code dealing with duplicates award ?

 

[Remou]

The awards appear to be sequential in the question and the code includes:

i = i + 1

The customers are randomly ordered and assigned a sequential number so how can the award be duplicated? What point have I missed?

http://lessthandot.com

 

[PHV]

OOps, sorry.
Anyway the AwardNumber don't seems to be a "sequential" number (100,200,...).
The OP should elaborate on the data schema.

 

[Remou]

Sequential" as in forming a sequence, in this case the sequence increments by 100 (

http://www.thefreedictionary.com/sequence),

however, it is true I should have included +100, rather than +1.

I agree that the OP should provide more information.

http://lessthandot.com

 

[MoBetter]

The AwardNumber is actually a list of numbers not in sequential order. I was trying to write a query to do the random generation from the list, so the db doesn't duplicate the same award. This would be helpful to run reports.

 

[MoBetter]

I'm sorry for not being clear. I really wanted the db to go into the specified table column (AwardID) and automatically assign any award from the (AwaredID) column to the customer (CustomerID) without assigning the award to more than one customer.

One award for one customer...

 

[Remou]

You can use the randomly ordered customer table and step through both the award table and the customer table. If the customer table is randomly ordered, even if the award is in sequence the order is random. Alternatively, you could randomly order the awards table and step through both tables in a similar way. I do not think that you can use SQL, because you have no matching columns, unless you wish to add a column to each table that can be assigned a random number between 1 and record count.

Sub Award()
Dim rs As DAO.Recordset
Dim rsA As DAO.Recordset
Dim i As Long

strSQL = "SELECT Rnd([CustomerID]) AS RandCust, t.Award " _
       & "FROM tblCustomer t " _
       & "ORDER BY Rnd([CustomerID])"
       
Set rs = CurrentDb.OpenRecordset(strSQL)

strSQL = "SELECT Award FROM tblAwards"
Set rsA = CurrentDb.OpenRecordset(strSQL)

Do While Not rs.EOF()

    rs.Edit
    rs!Award = rsA!Award
    rs.Update

    rs.MoveNext
    rsA.Movenext
Loop

End Sub

http://lessthandot.com

 

[PHV]

Remou, I'd add the tblCustomer's PK in the first strSQL.

 

[MoBetter]

Hey PHV what do you mean. Can you please describe or show where you would place this...?

Thanks

I am anxious about this and you two are narrowing this down. I have over 5000 awards that's why I used that example without being too verbose.

Again, if you could please describe a bit more.

Thanks

 

[NXMold]

Over 5000 awards, and can we assume many more than 5000 customers?

Seems like two recordsets are needed. A randomized customer list (limit to top 5,000?), and the awards.

Assign rsCust!award = rsAward!awardID, then advance both recordsets and repeat.

 

[MoBetter]

It is over 5000 customers; however, awards are only giving to 200 customers per month.

This means each month a new award needs to be assigned to a customer so when the next month arrives, the db doesn't assign an award that was already assigned in the previous month to a different customer.

 

[Remou]

You need something on the lines of:

strSQL = "SELECT CustomerID, Rnd([CustomerID]) AS RandCust, t.Award " _
       & "FROM tblCustomer t " _
       & "WHERE tAward Is Null " _
       & "ORDER BY Rnd([CustomerID])"

You will need to check that you have enough customers for awards and if not, restart the cycle.

http://lessthandot.com

 

[Remou]

Oops. You will need an award date field.

http://lessthandot.com

 

[MoBetter]

This may be a foolish question, but do I need to use ONLY the one that you just provided or add it to the one previously written? The reason I asked is because I tried to run the previous procedure and the following error occurred: “Invalid outside procedure.”

Here what I did. I tested the code on two small tables with one field name each:

FIRST TABLE

TABLE NAME: Subject Award

FIELD NAME: SubjectNumber


SECOND TABLE

TABLE NAME: CUSTOMERNAME

FIELD NAME: SubjectNumber

I used a QUERY to link the two TABLES together by FIELD NAME: SubjectNumber and called it CUSTOMER AWARD

I copied the code into a MODULE and named it RANDOM LINK

I created a REPORT from the QUERY: CUSTOMER AWARD then added the code onto the OPEN – [EVENT PROCEDURE] using the CODE BUILDER (Pressing the three (•••) buttons) When I tried to open the REPORT, the above message appeared.

When the message first appeared, I deleted the MODULE and the same message appeared again.

I know I may be annoying you by now, but PLEASE, PLEASE, PLEASE… What am I doing wrong? How do I get this to work?

I do THANK YOU for assisting.

 

[Remou]

I had thought that your table might be:

FIRST TABLE

TABLE NAME: Awards

FIELD NAME: AwardID

SECOND TABLE, includes all customers

TABLE NAME: CustomerAwards

FIELDS:
- CustomerID (foreign key to Customer table),
- AwardDate,
- AwardID (foreign key to Awards table)

You then paste the code to a standard module and run it, by choosing Run from the menu. You should use a backup copy of your database for testing and check everything before goining live. The code should be changed in line with the above tables, like so:

Sub Award()
Dim rs As DAO.Recordset
Dim rsA As DAO.Recordset

strSQL = "SELECT t.CustomerID, Rnd([CustomerID]) AS RandCust, t.AwardID, t.AwardDate " _
       & "FROM CustomerAwards t " _
       & "WHERE t.AwardID Is Null " _
       & "ORDER BY Rnd([CustomerID])"       
Set rs = CurrentDb.OpenRecordset(strSQL)

strSQL = "SELECT AwardID FROM Awards"
Set rsA = CurrentDb.OpenRecordset(strSQL)

Do While Not rs.EOF()

    rs.Edit
    rs!AwardID = rsA!AwardID
    rs!AwardDate = Format(Date(),"yyyy/mm/dd")
    rs.Update

    rs.MoveNext
    rsA.Movenext
Loop

End Sub

Your CustomerAward table will be updated with the award details. You can now join this table in a query to the customers table (using CustomerID) and the awards table (using AwardsID) to get all the details you need. You will need to filter the query by the date that you ran the code to get the latest batch of customers with an award. Eventually, nearly all the customers will have an award, and you may not have enough customers left for all the awards you have to present. You will have to decide what to do when this happens.

http://lessthandot.com

 

[MoBetter]

I got it to work. I needed to select the "Microsoft DAO 3.6 Object Library" under "Reference". Once I did that, the code worked.

THANKS

 

[MoBetter]

I have a question. Now that you all (Remou, PHV and NXMOLD) helped to get this to work, how do I apply it to a query, so that the code would run, behind the scenes, automatically when I apply an update/refresh button to a form?

 

[NXMold]

Call your sub from the OnClick event of the button:

Private Sub btnRefresh_OnClick ()
Award
End Sub

The Award sub must be in the same module, or declared as a public sub.