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.
126 6787
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.
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
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.
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.
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?
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.
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.
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
-
-
It's hard to say what's wrong without the error message.
When I went to debug this is where it said the error was:
DoCmd.RunSQL strSQL
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
Which piece are you having trouble with? The time factor or the unique identifier?
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.
Alright here is where I'm at. Also if you see any tweaks I should do let me know.
Thanks
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????
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.
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.
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.
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?
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.
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.
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.
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
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.
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.
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.
Tried it a different way anr all I get is the same number. See attached picture for code and setup
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.
Field: Count Of LPA Report Level 1: Count(*)
Total: Expresion
All it returns is a 1 for each record.
I tried it several ways until I seen that you can do these things through the query wizard.
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;
-
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
-
and this is what I mean about fields to fill - Rank («dNumber», «sTableName», «sFieldName», «bAscending», «sWhereCondition»)
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.
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.
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.
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.
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];
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.
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];
That base query still removes way more than I said to remove for the base query.
Ok, I'll put everything in but the date of audit and rank.
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?
What happened to the random code and the ordering?
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;
But there exists no R field yet. We are talking about the base query. The query that starts it all off.
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;
Still need help with this. What would my next step be????
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Wayne |
last post by:
I've been clicking around Access 2007 Beta 2 and can't see the custom
menu bar designer. Is it in the beta? Maybe I'm blind. The question
that comes to mind is: Will custom menu bars be the same...
|
by: Paul H |
last post by:
Why the heck was this dropped? All my dbs use ULS even the complex ones. It
aint perfect, it's too fiddly, but it works.
How will I be able to say "These users can run these reports and these...
|
by: prn |
last post by:
Hi folks,
I'm relatively new to Access, but I seem to have drawn the short straw, so I have the assignment for my workplace of looking for problems/inconsistencies in migrating applications to...
|
by: Neil |
last post by:
A client of mine likes some of the new bells and whistles in Access 2007,
and is thinking about converting our A03 format MDB to an A07 format file.
However, while some of the users have A07, many...
|
by: Neil |
last post by:
Just found out that the Microsoft Rich Textbox does not support full text
justification, since it's based on Version 1.0 of the RichEdit Window Class,
and full text justification is only available...
|
by: tony.abbitt |
last post by:
I have recently installed Office 2007 (SP1) retaining the previous
installation of Office 2003.
I have converted an Access 2003 database to Access 2007. The database
contains the VBA code...
|
by: wevans |
last post by:
Hello all.
I'm new to access and am trying to create a calendar/schedule in access. Yes I know there's a bunch of other ways and software to use, but I want to keep it together with the database I...
|
by: sphinney |
last post by:
My company is in the process of upgrading everyone to Access 2007. Since my machine was upgraded I've been highly frustrated with an apparent random behaviour byAccess that I can't understand. It...
|
by: prakashwadhwani |
last post by:
Hi !! I'm about to develop a new project for a client.
Should I go about it in Access 2003 or 2007 ? Purchasing it either
for me or for my client is not a major consideration here ... what I'd...
|
by: ARC |
last post by:
I think I made a major blunder, and now Access 2007 no longer opens. I made
the mistake yesterday of using the "test" option in wise installer for my
Access 2007 runtime app. It actually installed...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |