I have the following query in Excel that I use to rank points against a queue:

=SUMPRODUCT(--($G$4:$G$4431=$G4)*($J4>$J$4:$J$4431))+1

Column G = Queue Name
Column J = Points

the query above is what i have in column 4. What I would like to do is run a query in access that would do the same.

hope that makes sense. Can somebody please help.

Thanks in advance
aftabn10

Could you please move this to the Excel section of the forum :)

Could you please move this to the Excel section of the forum :)

It is not an Excel question but it would be better if the poster explained in English with field names.
It looks like an If construct.

Brian

Isn't SUMPRODUCT an Excel function? It's equivalent to an array + summing based on certain criteria.

Yes sumproduct is an Excel function, but he wants to do the "same" in ACCESS.

Brian

Ah yes, didn't see that bit. Thanks Brian. Apologies to the OP.:) So yes, you were right (as always) re the IIF() function and combining that with SUM() and the > (greater than) operator.

Ok, I have now managed to write an SQL Query in Access in order to rank data. The following is the query I have written:

SELECT a.[Q Name], a.[Q Time], Count(*) AS rank
FROM (SELECT [Q Name], [Q Time]
FROM Table1
GROUP BY [Q Name], [Q Time]) AS a
INNER JOIN (SELECT [Q Name], [Q Time]
FROM Table1
GROUP BY [Q Name], [Q Time]) AS b ON a.[Q Name] = b.[Q Name]
GROUP BY a.[Q Name], a.[Q Time]
ORDER BY a.[Q Name], Count(*);

When i select just one queue, to view the ranking, i get the same ranking for each data i.e.

Queue1 | 00:22:12 | 12
Queue1 | 00:21:12 | 12
Queue1 | 00:20:12 | 12
Queue1 | 00:19:12 | 12
Queue1 | 00:18:12 | 12
Queue1 | 00:17:12 | 12
Queue1 | 00:16:12 | 12
Queue1 | 00:15:12 | 12
Queue1 | 00:14:12 | 12
Queue1 | 00:13:12 | 12
Queue1 | 00:12:12 | 12
Queue1 | 00:11:12 | 12

Could somebody please help, as i dont know where i have gone wrong.

Thanks in advance.

What does the results look like when run in Excel?

Can you provide a sample xls?

David

Thanks for your response DCrake. I have attached a sample spreadsheet which shows the excel formula that I have used alongside the Access Ranking Formula.

Thanks once again.

Dug around a bit and found this snippet that may help you

Something like
MyRankQuery

SELECT MyTable.ABS, DCount("*","MyTable","[ABS]<=" & [ABS]) AS Rank
FROM MyTable
GROUP BY MyTable.ABS, DCount("*","MyTable","[ABS]<=" & [ABS]);


Then make another query that joins the table to the first query.
FinalQuery

SELECT MyTable.MyDate, MyTable.ABS, MyRankQuery.Rank
FROM MyTable INNER JOIN MyRankQuery ON MyTable.ABS = MyRankQuery.ABS;

Note: that if there are two or more identical ABS scores then they will both be ranked the same (so tied for 3rd means the rank given to both is 4th)

Another solution that someone came up with was to annd the query to a atable that has an auto number and get it to start from 1 when appending. Then view the table in descending order.

That way ther will be no joint places, if that's what you want.

David

Only problem with the second solution is when a record is deleted, the ranking then doesn't correlate.

Just to clarify aftabn10, you are ranking based on the time field (in descending order) right?

vbaInet, thats correct I will be ranking on the time field...

DCrake, i have wrote the following:

SELECT AHTVolData.[Agent AHT for this Q], DCount("*","AHTVolData","[Agent AHT for this Q]<=" & [Agent AHT for this Q]) AS Rank
FROM AHTVolData
GROUP BY AHTVolData.[Agent AHT for this Q], DCount("*","AHTVolData","[Agent AHT for this Q]<=" & [Agent AHT for this Q]);

but when i try and run this query i get the following error:

Syntax error (missing operator) in query expression '[Agent AHT for this Q]<=00:03:24'.

Any ideas where i have gone wrong?

Thanks once again.

As you are using times they need to be encompassed in #'s

","[Agent AHT for this Q]<=#" & [Agent AHT for this Q] & "#" )


David

thanks for that dcrake.

DCrake, I have ran the 2 queries you pasted, but this does not rank the times for each queue and ranks all the times as a whole. Just to make sure the "ABS" part of the query was the actual time right?

Does this work

select [q name],[q time],(select count(*) + 1
from table1
where [q name] = x.[q name]
and [q time] > x.[q time] ) as rank
from table1 as x
order by [q name],[q time] desc

Brian

Was that a yes or a no?
Have you solved this?

Brian

Brian, thanks for that, worked brilliantly, just tried it now, been away for most of the day...