Ranking Query in Access 2007

[DJ Rhino]

I looked all over the net and seen answers but I am having trouble understanding how to do it. If I attach my database would someone take a look at it and tell me what I need to do next to get what I want?


I need the level 1 auditors dates to schedule them monday through friday for each shift. for example

Auditor level 1 shift 1 3-2-2015 (random Name Here)
Auditor level 1 shift 1 3-3-2015
etc.

I will run the schedule on friday for the next week. Also if there is a scheduled holiday on a day an audit is scheduled I need it to call out the holiday for that date, which I have a table for the holidays.

Thanks for any and all help you will provide. Please be paitent as I have been trying to figure this out for a while now and got myself so confused.

[June7]

Thread title refers to Ranking Query but I am not sure that is what you need. Actually don't understand what you are trying to do. Post an example of desired result.

I see you have code. What happens - error message, wrong results, nothing?

[DJ Rhino]

From my understanding is the rank is going to help set the date. I attached a pdf file thats close to what the desired result will look like. I guess once I get the rank I will need to figure out the code to get the date to advance for each auditor.

[DJ Rhino]

If you open up the database and run through make schedule and then look at the report, that will show you a little closer to what I need.

[June7]

I understand the VBA procedure is intended to assign ranking. What logic are you trying to program? What are the rules for assigning ranking? If you were doing this on paper with pencil, how would you do it? What are the steps involved in this process?

If the report shows what you need, then what is the issue?

[DJ Rhino]

The report I showed is what it use to look like. I needed to create a whole new system to replace that one. The person who made that one doesn't do this kind of work here anymore and I could not figure out how to make the nessasary changes to that system, so I made a whole new one. I was told if I make a ranking query that I could take the rank and have that advance the date by the ranking number. If you play with the database I attached you will see I'm close. I just need help with getting over this hump. I'm hopeing that maybe you will have a better way of doing the date then what I was told.

[June7]

I still need you to describe the process. What have you attempted - where shall I see that you are 'close'? What queries? I am working in a vacuum - need some orientation.

[DJ Rhino]

I have made a query called Level Level 1(Make Table) to make a table called "Ranking". This is where I'm stumped. I'm told to make a query based on "Ranking" table and join itself so it will give me the ranks. I have tried this and it doesn't seem to work. This is the code that I have so far, not sure if its right or not, and don't know what to do next.

SELECT Ranking.Entrynumber, Ranking.FirstName, Ranking.LastName, Ranking.Shift, Ranking.Level, Ranking.Alternate, Ranking.Date, Ranking.Inactive, Ranking.R
FROM Ranking AS Ranking_1 LEFT JOIN Ranking ON Ranking_1.Entrynumber = Ranking.Entrynumber;

[June7]

Worse than pulling teeth! Not giving much info. Why are you using Rnd() function? Please give a thorough explanation of the logic you are trying to code.

Review this article about ranking records http://allenbrowne.com/ranking.html

[DJ Rhino]

I am using this database as a random scheduler for an internal audit system.I have the level 3 and 2's all set as they only are done once per week. The problem is the level one audits. I need 1 auditors per shift per day. This is where the date problem comes in. I hope this was enough info on what I'm try to accomplish, if not I can try to provide more info as needed.

[June7]

You need query/code that will randomly assign individuals from Auditors (only those identified as Level 1) table to each of 3 shifts each day? Should there be an even distribution of people across shifts?

What is the Alternate field for?

The Level1 query runs without error. Individuals are listed with Shift and Level and Alternate and date. What is wrong with the process in place?

What are the rules for this process? That's the kind of info I need.

Then again, this may be way over my head and hopefully someone with more insight will contribute.

[DJ Rhino]

We rotate people to do audits and we like doing it randomly. 1st will always do first shift audits.
The alternates are for if the person assigned to an audit isn't at work for whatever reason, the alternate is responsible for doing the audit.
The date is whats wrong, they need to be done on different dates, other wise they will do it on the same day.
The problem was that we needed to add another level and I could not do that with what we had in place, that and a lot of things were stripped away.

Not sure about the rules, I have a table for all of the Auditors what level they are and shifts. Level 2 and 3 are upper level managers and perform theirs once a week, thats why I have no issue with them.
Level 1 auditors are assigned at random on their own shifts, that way no one can complain that they are being picked on to do more audits then some one else.

I'm close to having this complete other than the date section needs to be code that the 5 auditors on 1st shift are assigned a mon - fri type date and the same for 2nd and third shifts.

Hope that clears things up a little more.