Random Schedule for Access 2007

[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,D ate())

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/insights/954764-ranking-queries-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,D ate()) 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.