Random Schedule for Access 2007

[orange]

I'm not following your comment, this is SQL

SELECT [running lines].horse, [running lines].rctrack, [running lines].RCDate, [running lines].rcRace, [running lines].[Date]
FROM [running lines]
WHERE ((([running lines].[date]) In
 (
 select top 5 XX.[Date]  from
   [running lines] as XX
     WHERE XX.horse  = [running lines].horse 
     Order By XX.[Date] desc     ))
 )
GROUP BY [running lines].horse, [running lines].rctrack, [running lines].RCDate, [running lines].rcRace, [running lines].[Date]
ORDER BY [running lines].horse, [running lines].[Date] DESC;

[DJ Rhino]

Sorry they way it was structured on the links you sent me it looks more like VBA, but the code you posted I would say is SQL. My question is why is my code erroring out and am I using the right format? Can you put up code structured the way I will need to put it in

My sample:

Select [Query Name].Field name, next fireld name if needed, from [Query] Etc. is this example correct?

[DJ Rhino]

I attached the code as I understand how I think its suppose to be and the error message I get.

[orange]

See TOP N here

[DJ Rhino]

tried it that way also using this code based on that link like this:

SELECT Auditors Level 3 Query Random.Entrynumber, Auditors Level 3 Query Random.Firstname, Auditors Level 3 Query Random.Lastname, Auditors Level 3 Query Random.Level, Auditors Level 3 Query Random.Alternate, Auditors Level 3 Query Random.Date, Auditors Level 3 Query Random.Inactive, Auditors Level 3 Query Random.R
FROM Auditors Level 3 Query Random
WHERE Auditors Level 3 Query Random.R IN
(SELECT TOP 3 OrderID
FROM Auditors Level 3 Query Random AS Dupe
WHERE Dupe.R = Auditors Level 3 Query Random.R
ORDER BY Dupe.R DESC, Dupe.R DESC)
ORDER BY Auditors Level 3 Query Random.Entrynumber, Auditors Level 3 Query Random.Firstname, Auditors Level 3 Query Random.Lastname, Auditors Level 3 Query Random.Level, Auditors Level 3 Query Random.Alternate, Auditors Level 3 Query Random.Date, Auditors Level 3 Query Random.Inactive, Auditors Level 3 Query Random.R;

But I still get the same error. So obviously I'm still missing something. Where am I suoppose to put this code? In a new query or Module or form or table or same query to want this to work on?????I'm so confused.

[orange]

You can not have spaces in the names like you have.
I have no idea what you want from the data.
Can you tell me what exactly you're trying to do in plain English?
What do you want TOP of etc?

I have your database from earlier today.

Hmmm, What is OrderID in this?

(SELECT TOP 3 OrderID
FROM Auditors Level 3 Query Random AS Dupe
WHERE Dupe.R = Auditors Level 3 Query Random.R
ORDER BY Dupe.R DESC, Dupe.R DESC)

Is it true you only have 5 level 2 auditiors?

How many auditors, what levels are required for a shift?

[DJ Rhino]

Ok I changed the names of the queries to have no spaces and less wording.


What I need from the data is this:

Level 1 shift 1 - 5 Auditors per week at random
Level 1 Shift 2 - 5 Auditors per week at random
Level 1 Shift 3 - 5 Auditors per week at random

Level 2 - 2 Auditors per week at random

Level 3 - 1 Auditor per week at random

I seem to have the random selection part done, I just need to narrow down to the qty's from above.

The OrderID was something from the sample code that I missed removing.

Level is just a designation/Group Audit

Level 1 are hourly personel
Level 2 Supervisors/Managers
Level 3 Operational Managers

Once I get the coding done for the above section then I will need to give them a date of when they need to perform their Audit, unless its a holiday then the name of the holiday will need to show instead of an auditor.

I hope this is clear enough.

Thanks,

[orange]

The more I look at the task at hand, the more I think you may be better off with some code behind a form. In reality you have a certain number of Auditors at certain levels and shifts. You need specific numbers of Auditors at specific levels for shifts.
So, you can always find the number of Level X auditors, and you know haw many you have to assign.

A small randomizer can easily pick Y numbers out of X candidates (auditor/level).

I have posted info on the forum here. I think it would be more straightforward and could solve your issue.

[DJ Rhino]

I attached the error I got along with the code. I put this into a form/subform, I must have did something wrong. I tried to follow the code from the link that you showed me.

Here is the code in text form:

SELECT Level3Random.Entrynumber, Level3Random.FirstName, Level3Random.LastName, Level3Random.Level, Level3Random.Alternate, Level3Random.R, Level3Random.Date, Level3Random.Inactive FROM Level3Random ORDER BY Level3Random.R Where((([Level3Random].[R]) in
(select top 1 XX.[R] from [Level3Random] as xx Where xx.R = [Level3Random].R Order By XX.R dexc))) Group by [Level3Random].Entrynumber,[Level3Random].FirstName, [Level3Random].LastName, [Level3Random].Level, [Level3Random].Alternate, [Level3Random].R,[Level3Random].Date, [Level3Random].Inactive

[DJ Rhino]

Tried something different, but still did not work. I attached what I did as a pdf. I feel I must be getting close.

[DJ Rhino]

Wow I figured it out there is a spot in the queries options for top records......but now with that said, I still need help with the code for doing if New years(Date) = the date of an audit that it does not schedule an auditor but list the holiday instead.

[orange]

Did you get something working?

I think you might consider a form to help you rather than a form or sql to do it all.

If you have 20 level 1 auditors, and you need to randomly select 5 of them to work a shift per week to work on audits. And they are all available (or from those who are available) then perhaps something like

Select 15 level 1 auditors randomly, assign them to you audit schedule. Your schedule should respect holidays. If this is the only criteria for shift assignment, then you could randomize the shifts for the week also.
Your shifts could be
Day1 shift1
Day1 shift2
Day1 shift3
Day2 shift1..... There would be 15 of these for a full work week.

You could select 15 random numbers between 1 and 20 --these would relate to the level1 auditors.
Then assign auditor to your dayXshiftY based on the number.
In reality you could select 90 random numbers between 1 and the total available level1 auditors and do assignments for 6 weeks at a time.

Similarly select your level2 and level3 auditors.

I don't know how feasible or practical this approach might be, since it's possible 1 auditor could be assigned Day1 shift1 and also Day1shift3 etc and other "unwanted combinations".

You might want to think about the details and some constraints that could make random assignments more palatable. I'm sure others have dealt with similar issue of assigning staff to work/workshifts.

Good luck.

[DJ Rhino]

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 1 Shift 1 - 2-24-2015
Auditor 1 Shift 1 - 2-25-2015
Auditor 1 Shift 1 - 2-26-2015
Auditor 1 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.

[orange]

?? I tried your startup form to create a schedule.
It deleted info and asked to overwrite, then it said couldn't find something...
Your post shows Auditor 1 shift1 for every day -- how random is that?


I could be misunderstanding your nearly complete product but
I think you should work what you want to achieve on a piece of paper (logic /dfd)
when you get a temp design that works, then move to a database.

[DJ Rhino]

Sorry copy paste got me...Should be this:

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

I have it deleting tabels then recreating them as we will not be storing them in the database. I have a message box pop up to let the person that is running the schedule to it ran. I says schedule complete. Then for the report I have it exporting out to .PDF and saving in to a location of the users choice.