Random Schedule for Access 2007

[DJRhino]

I need help, I have never tried anything like this before. I have set up some tables, queries and forms. What I need help with is picking peoples names at a certain quanity for each category/shift per week.

From LVL 1 I need 1 person per shift per day per week(5 from 1st, 2nd and 3rd.

From lvl 2 I need 2 per week

From LVL 3 need 1 per week.

Then I'll create a report to send out.

I have attached what I have done so far.

Thanks in advance.

[Rabbit]

Sorry, I don't download and open attachments from people I don't know. But you can use the Rnd() function and seed it with a time factor and a unique key. If you sort by that and take only the first n records that you need, that will get you the random selection you need.

[DJRhino]

As stated above, never done a random schedule, I know nothing about a Rnd() or where to put it or what time factor or unique key. When this is set up I need it to run as a macro so my user can just click a button and have it output to a report to be mailed out with in the company.

Thanks,

DJRhino

[DJRhino]

I also forgot to add that we have a holiday list that it needs to pull from so it doesn't schedule anyone on those days.

[DJRhino]

Here is the database we currently use made by someone else, but trying to reverse enginer it has been a pain. but works a little.

[DJRhino]

I tried the Rnd() function but I ended up getting the same number for each record. Did I miss something?

R: Int ((5 - 1 + 1) * Rnd + 1)

I used this in my level 1 table. What did I do wrong and how can I have this do this per shift, also how do I get the syntax code to pick the 5 from each shift?

[Rabbit]

If you seed it with the same number every time, it will give the same results every time. That's why you include a time factor along with a factor unique to the record.

Selecting 5 records can be done using the top 5 syntax.

[DJRhino]

Ok, so how do you do that? Can you give me an example how it would look like in sql? Would I use the Now() for the time stamp? I did study the material that you sent me and did some youtubeing which helped a little. I think I need to sign up for some classes.

[DJRhino]

Ok so I started to do a little coding on this but I get an error when I try to run it, can you tell me what i did wrong? thanks

Option Compare Database

Sub PickRandom()
   Dim db As DAO.Database
   Dim tdf As DAO.TableDef
   Dim fld As DAO.Field
   Dim rst As DAO.Recordset
   Dim strSQL As String
   Dim strTableName As String

' 1: Create a new temporary table containing the required fields
    strSQL = "SELECT Auditors Level 1.Firstname, Auditors Level 1.Lastname " & _
             "INTO tblTemp " & _
             "FROM Auditors Level 1;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    
' 2: Add a new field to the new table
    Set db = CurrentDb()
    Set tdf = db.TableDefs("tblTemp")
    Set fld = tdf.CreateField("RandomNumber", dbSingle)
    tdf.Fields.Append fld

' 3: Place a random number in the new field for each record
    Set rst = db.OpenRecordset("tblTemp", dbOpenTable)
    rst.MoveFirst
    Do
        Randomize
        rst.Edit
            rst![RandomNumber] = Rnd()
        rst.Update
        rst.MoveNext
    Loop Until rst.EOF
    rst.Close
    Set rst = Nothing
    
' 4: Sort the data by the random number and move the top 25 into a new table
    strTableName = "tblRandom_" & Format(Date, "ddmmmyyyy")
    strSQL = "SELECT TOP 5 tblTemp.Firstname, tblTemp.Lastname " & _
             "INTO " & strTableName & " " & _
             "FROM tblTemp " & _
             "ORDER BY tblTemp.RandomNumber;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

' 5: Delete the temporary table
    db.TableDefs.Delete ("tblTemp")
End Sub

[Rabbit]

It's hard to say what's wrong without the error message.

[DJRhino]

When I went to debug this is where it said the error was:

DoCmd.RunSQL strSQL

[DJRhino]

Nevermind on the last couple of posts. I restructred how the tabels are set up. I combined the tabels into one as Rabbit suggested. I'm using the queries to filter the different levels. I don't know enough VBA to use it. So I'm going back to try and use SQL. So I have the random SQL code in the queries but now I need to figure out how to get each entry to have a different number and then pick the top * number. Can I get so specific help with this?

Thanks

[Rabbit]

Which piece are you having trouble with? The time factor or the unique identifier?

[DJRhino]

Both...How and what do I need to put in the SQL code? I will upload tomorrow when I get back to work what I have changed so you can look. I'm very green when it comes to SQL and VBA.

[DJRhino]

Alright here is where I'm at. Also if you see any tweaks I should do let me know.

Thanks