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

[DJRhino]

I got the random number fixed, and figured the code for selecting the top 5, but i get an error running this code in my query

SELECT TOP 5 [Entrynumber] FROM [Auditors Level 1 Shft 1];

It keeps highlighting Select and telling me to inclose it.

What am I doing wrong????

[Rabbit]

Sorry, I don't download and open attachments from people I don't know.

I don't see the random code in the SQL. Please post the full error message along with the error number.

[DJRhino]

Here is the nearly complete product. I need some help with the database putting in a date when they need to do their audit. For example

Level 3 is based on 1/week, so it will be the week of(I schedule once a week)
Level 2 Same as above
Level 1 I will need a different date for each auditor/Shift eg.

Auditor 1 Shift 1 - 2-23-2015
Auditor 2 Shift 1 - 2-24-2015
Auditor 3 Shift 1 - 2-25-2015
Auditor 4 Shift 1 - 2-26-2015
Auditor 5 Shift 1 - 2-27-2015

Then repeat same process for shifts 2 and 3

Then if there is a holiday I will need it to schedule that holiday instead of an auditor.


Thanks for the help.

[Rabbit]

Again, I don't download and open attachments from people I don't know so I have no idea what your queries look like. You need to post the SQL code in the thread itself.

As far as the dates go, you can rank the random selection from 1 to 5 and then use the DateAdd() function to add that many days to your chosen date.

[DJRhino]

Here is the code I used and it works fine, but it gives me the same date for each record. I need it to advance 1 day for each auditor. Just level 1 auditors as level 2 and 3 are done by week and I have those done right.

Expr1: DateAdd("d",1,"02-Mar-15")

Would I add a field to this expresion like ID????

Also will I have to go and change that date in the code every week to get it to advance the following week?

[Rabbit]

You did the DateAdd portion of my suggestion but you didn't rank the records returned. The ranking is important because it returns how many days to add.

You can use the Now() function to return the current date so you don't have to change the query every week.

[DJRhino]

Pleas explain how I go and rank the records.So if I do the schedule on Friday The Code should look like this:

Expr1: DateAdd("d",2,Date())

I used date() because I dont need the time.

[Rabbit]

Using Date() works as well.

For the ranking, please read our article here:
http://bytes.com/topic/access/insigh...-ms-access-sql

[DJRhino]

I looked over you article and I'm way confused. Could you take one of the examples and put into this string in this format - Expr1: DateAdd("d",2,Date()) Without the [code/] button from above, it really confuses me. Also a more of a step by step of what I put in the feild line. I'm a lot more of a rookie than I first thought, I have no formal training.

[Rabbit]

It's not possible to do from just the query designer in Access because of the complex join, therefore there's no expression that I can give you to just plop into the query builder. You will need to do it through SQL code.

Here's a link to a good SQL tutorial: http://www.w3schools.com/sql/sql_intro.asp

[DJRhino]

Is it possible for a function to be made using vba and then add that function to the query? if not how about the proper steps to follow to do the ranking...Build new query based on XXX table and so forth.

[Rabbit]

You can use VBA, but if you were going to use VBA, you may as well code the entire thing in VBA instead of mixing VBA with a SQL solution. It's problematic creating a ranking from randomized data using VBA and SQL. So if you want to do a full VBA solution instead, go for it and post your code if you run into trouble along with the error messages you get.

If you want to do the SQL solution instead, take your random query and use that as the base table in the tutorial I linked earlier. The steps on how to create a ranking query are in that tutorial. There's not much more detail I can get into that isn't already in the tutorial. If you're having trouble with the query, you'll need to post the SQL code so we can diagnose the problem.

[DJRhino]

Still cant get it to work. I built a new query called Rank Query and based it off of a table with the random expresion. All I get is a Syntax error. I attached a screen shot of the query and error.

[DJRhino]

Tried it a different way anr all I get is the same number. See attached picture for code and setup

[Rabbit]

I don't know how many times I have to tell you that I don't download and open attachments from people I don't know.

You need to copy and paste the SQL code into the thread itself and type out the error messages that you get.

[DJRhino]

Field: Count Of LPA Report Level 1: Count(*)
Total: Expresion

All it returns is a 1 for each record.

[DJRhino]

I tried it several ways until I seen that you can do these things through the query wizard.

[DJRhino]

Here is the entire code for the query I'm trying to do this in(Just learned where to find this)

INSERT INTO [LPA Report Level 1] ( Entrynumber, FirstName, LastName, Shift, [Level], Alternate, R, [Date], Inactive, DateOfAudit, Rank )
SELECT TOP 5 Auditors.Entrynumber, Auditors.FirstName, Auditors.LastName, Auditors.Shift, Auditors.Level, Auditors.Alternate, ((25-1+1)*Rnd([entrynumber])+1) AS R, Auditors.Date, Auditors.Inactive, DateAdd("d",0,Date()) AS DateOfAudit, Count(Auditors.Entrynumber) AS CountOfEntrynumber
FROM Auditors
GROUP BY Auditors.Entrynumber, Auditors.FirstName, Auditors.LastName, Auditors.Shift, Auditors.Level, Auditors.Alternate, ((25-1+1)*Rnd([entrynumber])+1), Auditors.Date, Auditors.Inactive, DateAdd("d",0,Date())
HAVING (((Auditors.Shift)=1) AND ((Auditors.Level)=1) AND ((Auditors.Inactive)=0))
ORDER BY Count(Auditors.Entrynumber) DESC;
 

[DJRhino]

I also tried this as a function in a query, but did not know how to fill in the nessasary fields in the builder function.I got this from searching the web.

Function Rank(dNumber As Double, sTableName As String, sFieldName As String, Optional bAscending As Boolean = True, Optional sWhereCondition As String) As Long
     'Version    1.0
     'Created    12/07/2010 22:10    Apollo67
     'Purpose    Emulates MS Excel's Rank() function, for use in queries
     '
     'Description:
     'Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list.
     '(If you were to sort the list, the rank of the number would be its position.)
 
     'Inputs:
     'dNumber = The number whose rank you want find
     'sTableName = The current table name
     'sFieldName = The field of numbers to be evaluated
     'bAsceding = Optional ranking order - True for Asceding Else False
     'sWhereCondition = an optional criteria constraint to query records on
 
     On Error GoTo Err_Rank
 
     Dim rs As DAO.Recordset
     Dim db As Database
     Dim sSQL As String
     Dim sWhere As String
     Dim sOrderBy As String
     Dim lReturn As Long
     Dim lRow As Long
     Dim dLastValue As Double
     Dim dCurrValue As Double
 
     sSQL = "SELECT " & sFieldName & " FROM " & sTableName
     sOrderBy = " ORDER BY " & sFieldName & IIf(bAscending, "", " DESC")
     If (Len(sWhereCondition) > 0) Then sSQL = sSQL & " WHERE " & sWhereCondition
     sSQL = sSQL & sOrderBy
 
     Set db = CurrentDb
     Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
 
     If (rs.RecordCount > 0) Then
         lRow = 0
         Do While Not rs.EOF
             dCurrValue = CDbl(Nz(rs.Fields(0), 0))
             If (dLastValue <> dCurrValue) Then lRow = lRow + 1
             If (dNumber = dCurrValue) Then
                 lReturn = lRow
                 Exit Do
             End If
             dLastValue = dCurrValue
             rs.MoveNext
         Loop
     End If
     rs.Close
 
Exit_Rank:
     Set rs = Nothing
     Set db = Nothing
     Rank = lReturn
     Exit Function
 
Err_Rank:
     'Optionaly handle error as necessary
     'Generic Error Handler
     MsgBox "Error: " & Err.Number & vbCr & vbCr & Err.Description, , "Error In Procedure Rank()"
     Resume Exit_Rank
 
 End Function
 

[DJRhino]

and this is what I mean about fields to fill

Rank («dNumber», «sTableName», «sFieldName», «bAscending», «sWhereCondition») 

[Rabbit]

As I said previously, mixing SQL and VBA with random data doesn't usually work. You need to pick one or the other. Let me know which approach you want to take and I can look at the code to tell you what's wrong with it.

[DJRhino]

Lets go the query route, I only added the VBA code cause you create a function that could be used in a query, but i would definitly rather use the query.

[Rabbit]

Ok, here's what needs fixing in the query:

1) Is Entrynumber a unique numeric field? If not, you will need something like that for the random.

2) I don't see where you incorporate a time factor into the random function so you get different values each time it is run. As it currently stands, you will always get the same "random" value.

3) There's no need for the Date of Audit or Rank field in this base query as that will need to be calculated in the ranking query.

4) I'm not sure why you're returning the count of rows, it will always be 1. Which also means you don't need a group by and the having CLAUSE should be a WHERE clause.

5) The ordering should be on the random expression, otherwise the selected 5 will always be the same.

Once you fix these issues in the base query, then you can work on the ranking query and assigning a date.

[DJRhino]

1)EntryNumber is an autonumber field, so it is unique.
2)Not sure what you mean by time factor. Everytime I have ran this the random function works real well, but I will be more than happy to tweak it if you can tell what and where in the code that I will need to put in this time factor.
3)I will at least need the date of audit fiel in this query as this is my append query. It appends data to a table I base my report on.
4) I will remove this from this query and try building one with the EntryNumber and FirstName Field. Should I have others in this base query?

5)I have changed this so it will sort desending on random.

I look forward to your response as I think you might have final cracked my tough shell of a head.

[DJRhino]

This is the base query I built as mentioned above. Whats my next step?

SELECT [LPA Report Level 1].Entrynumber, [LPA Report Level 1].FirstName
FROM [LPA Report Level 1];

[Rabbit]

2) This ((25-1+1)*Rnd([entrynumber])+1) always returns the same "random" number for a particular entrynumber regardless of when you run it. If you want truely random, you need to incorporate time factors. Also, all the constants you put in there are not needed.

Rnd((DatePart("n",NOW()) * DatePart("s",NOW()) + 1) * [entrynumber])

By incorporating seconds and minutes into the random function, this will cause it to assign different random numbers depending on when you run it. You can incorporate more or less time factors as you want.

3) The query is not ready for you to insert the Date of Audit. This is the base query we are talking about. The next query is the one you want to insert into the table for your report. We haven't gotten there yet so you should not be inserting anything into your final table. Instead, use a staging table for this base query.

4) I think you took it a little too far, I didn't say get rid of everything from the query, just the stuff I mentioned.

[DJRhino]

Thanks for the help on the random issue. I did not get rid of everything. I created a new base query. The code I sent you was for the append query I would use to make my table for the report. I will add the code that you mention for randomizing and take a look at how it changes things. Once again here is the code for Count/Rank base query:

SELECT [LPA Report Level 1].Entrynumber, [LPA Report Level 1].FirstName
FROM [LPA Report Level 1];

[Rabbit]

That base query still removes way more than I said to remove for the base query.

[DJRhino]

Ok, I'll put everything in but the date of audit and rank.

[DJRhino]

Here is the base query:
SELECT [LPA Report Level 1].Entrynumber, [LPA Report Level 1].FirstName, [LPA Report Level 1].LastName, [LPA Report Level 1].Shift, [LPA Report Level 1].Level, [LPA Report Level 1].Alternate, [LPA Report Level 1].R, [LPA Report Level 1].Date, [LPA Report Level 1].Inactive
FROM [LPA Report Level 1];

So whats my next step?

[Rabbit]

What happened to the random code and the ordering?

[DJRhino]

Field "R" is the field with random

SELECT [LPA Report Level 1].Entrynumber, [LPA Report Level 1].FirstName, [LPA Report Level 1].LastName, [LPA Report Level 1].Shift, [LPA Report Level 1].Level, [LPA Report Level 1].Alternate, [LPA Report Level 1].R AS Random, [LPA Report Level 1].Date, [LPA Report Level 1].Inactive
FROM [LPA Report Level 1]
ORDER BY [LPA Report Level 1].R DESC;

[Rabbit]

But there exists no R field yet. We are talking about the base query. The query that starts it all off.

[DJRhino]

Sorry, that whole thing was me going back to being stupid....This is the before my make table query and my append query. This is the one you want right?

SELECT Auditors.Entrynumber, Auditors.FirstName, Auditors.LastName, Auditors.Shift, Auditors.Level, Auditors.Alternate, Auditors.Date, Auditors.Inactive, Rnd((DatePart("n",Now())*DatePart("s",Now())+1)*[entrynumber]) AS R
FROM Auditors
WHERE (((Auditors.Level)=1))
ORDER BY Rnd((DatePart("n",Now())*DatePart("s",Now())+1)*[entrynumber]) DESC;

[DJRhino]

Still need help with this. What would my next step be????