Random Schedule for Access 2007

[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.Entryn umber, Auditors.FirstN ame, Auditors.LastNa me, Auditors.Shift, Auditors.Level, Auditors.Altern ate, Auditors.Date, Auditors.Inacti ve, Rnd((DatePart(" n",Now())*DateP art("s",Now())+ 1)*[entrynumber]) AS R
FROM Auditors
WHERE (((Auditors.Lev el)=1))
ORDER BY Rnd((DatePart(" n",Now())*DateP art("s",Now())+ 1)*[entrynumber]) DESC;

[DJRhino]

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

[Rabbit]

Insert the results of that query into a new table and then follow the instructions in the ranking article I linked earlier to create a ranking query from that new table.

[DJRhino]

Ok, so I did a make table query and made a table called ranking. Is my next step to make a query based on the origan table and new table??????

[DJRhino]

SELECT Auditors.EntryNumber, Auditors.FirstName, Auditors.LastName, Count(*) AS Rank
FROM Ranking AS Auditors, Ranking
GROUP BY Auditors.FirstName, Auditors.LastName, Ranking.Entrynumber
ORDER BY Count(*) DESC;

Here is what I have for the SQL Statement but I get some sort of error. Here is the error:

You tried to execute a query that does not include the specified expression 'EntryNumber' as part of an aggregate function

[Rabbit]

I don't see any of the join conditions that exist in the tutorial

[DJRhino]

As stated in my previous question, What am I suppose to join it to?????? The original "Auditors" and ????? The "Ranking" Table I just made?

[Rabbit]

In the tutorials, all the queries join the tables to themselves. And there's only one table in the queries, the table with the records you want to rank.

In your case, it's the Ranking table that you newly created from the base query. You have the correct table. You just don't have any of the join conditions.

[DJRhino]

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.R = Ranking.R) AND (Ranking_1.Inactive = Ranking.Inactive) AND (Ranking_1.Date = Ranking.Date) AND (Ranking_1.Alternate = Ranking.Alternate) AND (Ranking_1.Level = Ranking.Level) AND (Ranking_1.Shift = Ranking.Shift) AND (Ranking_1.LastName = Ranking.LastName) AND (Ranking_1.FirstName = Ranking.FirstName) AND (Ranking_1.Entrynumber = Ranking.Entrynumber);

OK here is what I got, I left joined all of the columns, not sure if that was right or not. If it is, whats the next step?

[DJRhino]

I tried the count total on EntryNumber, but that did not work right, so then I just tried the Sum total and it gives me what looks like the proper rank numbers, but it isn't, its just the same number as the entrynumber. So what do I need to do now?

[Rabbit]

You joined on all the key fields. But you have the incorrect join on the field that defines how the rows should be ranked. In this case, that field is R. Also, I don't see the count in your query. You will also need the corresponding group by on the key fields.

[DJRhino]

Ok, but where am I suppose to put the count and which join is wrong? Here is the current code:

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) AND (Ranking_1.FirstName = Ranking.FirstName) AND (Ranking_1.LastName = Ranking.LastName) AND (Ranking_1.Shift = Ranking.Shift) AND (Ranking_1.Level = Ranking.Level) AND (Ranking_1.Alternate = Ranking.Alternate) AND (Ranking_1.Date = Ranking.Date) AND (Ranking_1.Inactive = Ranking.Inactive) AND (Ranking_1.R = Ranking.R)
GROUP BY Ranking.Entrynumber, Ranking.FirstName, Ranking.LastName, Ranking.Shift, Ranking.Level, Ranking.Alternate, Ranking.Date, Ranking.Inactive, Ranking.R;