How to assign a value to a rank - Access 2010

[Lisa B]

Hello, I've been trawling the web trying to find how to build the expression to rank how many machines an individual has sold by date and to assign points for the ranks - so he who sold the most 6points, 2nd 4 points, 3rd 3 points, 4th 2 points 5th 1 point.

This is how my query is set out

Sales are stored in the table [Weekly Machines Sales Data]

the people are Vendeurs

When i have query

Rank: (select [Date], [fkVendeurid], Sum([Machines Sold]) as Sold
from [Weekly Machines Sales data]
Group by [Date], [fkVendeurid])

I'm getting an error

You have written a subquery that can return more than onefield without using the EXISTS reserverd word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field

(oo this is giving me a headache!) - thanks so much for the help

I have uploaded a zip with the db file in it here http://cid-4490afdc094900ec.skydrive...DC094900EC!368

I'm aiming towards having a ranking with value assigned for most machines sold and largest margin for each seller and for each team(equipe) for any given date period




Thank you

[Rabbit]

It's just as the error says. You can only return one field if you're going to use a subquery as a field. Also, when using subqueries as field, you must return only one row. Since you're doing a ranking field, I would think you would use a count and tie the subquery back to the outer query. But you don't seem to be doing this.

SELECT Salesperson,
     ((SELECT Count(*) FROM tblSales WHERE NumOfSales > x.NumOfSales) + 1) AS Rank
FROM tblSales AS x

[Lisa B]

thanks for the quick reply - the sales are not totalled by seller in the weekly sales table, that is done in the query - also i see no reference to date in your sugested code - are you able to use the field names as per the images i provided?

[Rabbit]

My code was just an example of how you would set up a ranking field. It has the basics of what is needed, you would have to modify it to your needs though.

I am unable to see the images. I think it is because of the browser I use at work.

[Lisa B]

Does your code work for a query as it looks like it's based on table data - are you able to view the pictures at a later time?

[Rabbit]

I may be able to view the pictures later but it would have to be much later.

Why don't you try adapting my query to fit your needs? My query was not based on your tables but fake tables I created.

[Lisa B]

I am unsure as to what the last x is after the final AS

so far i have written this

Rank: (Select [VendeurID], ((Select count(*) From [Weekly Machines Sales Data]
WHERE [Machines Sold] > [Weekly Machines Sales data].[Machines Sold])+1)
AS Rank
FROM [Weekly Machines Sales data])
AS

[Lisa B]

I have uploaded a copy of my dbase to skydrive http://cid-4490afdc094900ec.skydrive...DC094900EC!368

[Rabbit]

That doesn't match what I posted...

What you want is more like this

SELECT [VendeurID],
     ((SELECT COUNT(*) FROM [Weekly Machines Sales Data] WHERE [Machines Sold] > x.[Machines Sold])+1) AS Rank
FROM [Weekly Machines Sales data] AS x

[Lisa B]

I am unsure as to what the last x should be

all working perfectly accept i can't seem to sum the machines sold per vendeur per date

So i tried to adapt it

I have created two queries

Rqrysales

This sums the number of mahcines sold by each vendeur

I then use this query to create a new query called RankingMachines based on the Rqrysales query - created an alias of SumMach1 for the Rqrysales field list

To handle ties i created another query called Distinct where i chose the same two fields, summed the [Machines Sold] field, hid the [FKVendeurID] field and changed the properties of the query so that Unique Records were YES

In my RankingMachines Query i used the expression

Rank: (Select count(*) from [Distinct] Where [SumOfMachines Sold] > [SumMach1].[SumOfMachines Sold])+1

The only thing it's not doing is summing for the date

and it would seem now, not starting the ranking at 1

[Rabbit]

The x is an alias. It is needed for the subquery to be able to reference the outer query.

You can't do an aggregate query and ranking query together. You can rank the aggregate results after you aggregate them. But you can't rank the pre-aggregate and then sum them without losing rank.

[Lisa B]

I have managed to work out the problem with the summing for the dates, but the ranking is not starting at 1 if there is a higher sales value before that date

[Rabbit]

If you need a ranking grouped by date, you just need to account for that in the subquery's WHERE clause.

[Lisa B]

The grouping by date is fine

BUT the ranking is not starting at 1 if there is a sale of higher value earlier than the date chosen

i.e.

Sale Date 12/1/2011 - Value 12
Sale Date 14/1/2011 - Value 7
Sale Date 15/1/2011 - Value 4
Salte Date 16/1/2011 - Value 6

Ranking for date Range 13/1/2011 - 16/1/2011

Sale Date 14/1/2011 - Rank 2
Sale Date 15/1/2011 - Rank 4
Salte Date 16/1/2011 - Rank 3


If you select

[Rabbit]

I know. Account for it in the subquery's WHERE clause.

[Lisa B]

HOW?!

RqrySales

Weekly Machines Sales Data (table)
[Date de Vente]
[Machines Sold] Summed and Descending
[FKVendeurID]

Distinct - Properties set to unique value
Weekly Machines Sales Data (table)

[Machines Sold] Summed Descending
[Date de Vente] - not shown
[FKVendeurID] - not shown

RankingMachines Qry -
using Rqrysales - aliased new name SumMach1

[Date de Vente] - Criteria Between [Forms]![RankMachines]![StartDate] And [Forms]![RankMachines]![EndDate]

[SumofMachines Sold]
Rank:Rank: (Select count(*) from [Distinct]
Where [SumOfMachines Sold] > [SumMach1].[SumOfMachines Sold])+1

[Rabbit]

Put this extra condition in your WHERE clause.

DateField BETWEEN StartDate AND EndDate

[Lisa B]

have added another duplicate field in the query that has the ranking (as when you select where from the total row it un-shows it)

[Date de Vente] selected where for the total row and put the expression Between [Forms]![RankMachines]![StartDate] And [Forms]![RankMachines]![EndDate]


for a query of same startdate and endate i'm getting one result which is fine but rank 4!

[Lisa B]

mispost can' delete

[Rabbit]

I have no idea what you just said.

[Lisa B]

when i put your WHERE clause in the select statment for the rnaking

Rank:Rank: (Select count(*) from [Distinct]
Where [SumOfMachines Sold] > [SumMach1].[SumOfMachines Sold])+1


it doesn't work, so i put it in the criterira for the Date field in the query

[Rabbit]

It didn't work in the subquery most likely because your [Distinct] query doesn't return the date field as one of it's columns.

[Lisa B]

It is Summing Dates fine, however i now have the tie problem again :(

Rqrysales

SELECT Sum([Weekly Machines Sales data].[Machines Sold]) AS [SumOfMachines Sold], [Weekly Machines Sales data].FKVendeurID, [Weekly Machines Sales data].[Date de Vente]
FROM [Weekly Machines Sales data]
GROUP BY [Weekly Machines Sales data].FKVendeurID, [Weekly Machines Sales data].[Date de Vente]
HAVING ((([Weekly Machines Sales data].[Date de Vente]) Between [forms]![RankMachines]![StartDate] And [Forms]![RankMachines]![EndDate]))
ORDER BY Sum([Weekly Machines Sales data].[Machines Sold]) DESC;
 

RankingMachines

SELECT SumMach1.[SumOfMachines Sold], (Select Count(*) from [Distinct] Where [SumOfMachines Sold] > SumMach1.[sumofmachines sold])+1 AS Rank, IIf([rank]=1,6,IIf([rank]=2,4,IIf([rank]=3,3,IIf([rank]=4,2,IIf([rank]=5,1,0))))) AS Points, SumMach1.FKVendeurID
FROM Rqrysales AS SumMach1
WHERE (((SumMach1.[Date de Vente]) Between [forms]![RankMachines]![StartDate] And [Forms]![RankMachines]![EndDate]))
GROUP BY SumMach1.[SumOfMachines Sold], SumMach1.FKVendeurID;
 

Distinct

SELECT DISTINCT [Weekly Machines Sales data].FKVendeurID
FROM [Weekly Machines Sales data]
WHERE ((([Weekly Machines Sales data].[Date de Vente]) Between [forms]![RankMachines]![StartDate] And [forms]![RankMachines]![EndDate]))
GROUP BY [Weekly Machines Sales data].FKVendeurID;
 

[Rabbit]

What tie problem? You never mentioned a tie problem before this. If you're talking about records having the same rank because they have the same sum, then they should have the same rank. If you want to somehow force a different rank even though they're the same, you will have to resort to VBA coding.

[Lisa B]

SOrry thought I had mentioned it

Tie problem is when it is not adding the next rank number for tie, i.e

1
2
2
4
5
5
7

rather than
1
2
2
3
4
5
5
6
7

[Rabbit]

You'll need to return just the distinct dollar amounts for the ranking subquery to remove the duplication.

[Lisa B]

I need to Sum the number of machines sold per Vendeur for a date range - and rank who has sold the most within that date range

Currently Vendeurs now appearing more than once and not being ranked correctly

http://i305.photobucket.com/albums/n...machineres.png
DO i need two distinct queries?

Distinct

SELECT DISTINCT [Weekly Machines Sales data].FKVendeurID, Sum([Weekly Machines Sales data].[Machines Sold]) AS [SumOfMachines Sold]
FROM [Weekly Machines Sales data]
WHERE ((([Weekly Machines Sales data].[Date de Vente]) Between [forms]![RankMachines]![StartDate] And [forms]![RankMachines]![EndDate]))
GROUP BY [Weekly Machines Sales data].FKVendeurID;
 

RqrySales

SELECT Sum([Weekly Machines Sales data].[Machines Sold]) AS [SumOfMachines Sold], [Weekly Machines Sales data].FKVendeurID, [Weekly Machines Sales data].[Date de Vente]
FROM [Weekly Machines Sales data]
GROUP BY [Weekly Machines Sales data].FKVendeurID, [Weekly Machines Sales data].[Date de Vente]
HAVING ((([Weekly Machines Sales data].[Date de Vente]) Between [forms]![RankMachines]![StartDate] And [Forms]![RankMachines]![EndDate]))
ORDER BY Sum([Weekly Machines Sales data].[Machines Sold]) DESC;
 

RankingMachines

SELECT SumMach1.[SumOfMachines Sold], (Select Count(*) from [Distinct] Where [SumOfMachines Sold] > SumMach1.[sumofmachines sold])+1 AS Rank, IIf([rank]=1,6,IIf([rank]=2,4,IIf([rank]=3,3,IIf([rank]=4,2,IIf([rank]=5,1,0))))) AS Points, SumMach1.FKVendeurID
FROM Rqrysales AS SumMach1
WHERE (((SumMach1.[Date de Vente]) Between [forms]![RankMachines]![StartDate] And [Forms]![RankMachines]![EndDate]))
GROUP BY SumMach1.[SumOfMachines Sold], SumMach1.FKVendeurID;
 

[Rabbit]

Wow, your SQL is a mess, I'm having trouble understanding all of it.

Try this:

qrySumOfSales

SELECT [Weekly Machines Sales data].FKVendeurID, Sum([Weekly Machines Sales data].[Machines Sold]) AS [SumOfMachines Sold]
FROM [Weekly Machines Sales data]
WHERE ((([Weekly Machines Sales data].[Date de Vente]) Between [forms]![RankMachines]![StartDate] And [forms]![RankMachines]![EndDate]))
GROUP BY [Weekly Machines Sales data].FKVendeurID;

qryDistinctSales

SELECT DISTINCT qrySumOfSales.[SumOfMachines Sold]
FROM qryDistinctSales

qryRankSales

SELECT qrySumOfSales.FKVendeurID, qrySumOfSales.[SumOfMachines Sold],
((SELECT Count(*) FROM qryDistinctSales WHERE [SumOfMachines Sold] > x.[SumOfMachines Sold]]) + 1) AS Rank
FROM qrySumOfSales AS x

[Lisa B]

It didn't like the qryRankSales, I think because I had to alias the qrySumOfSales table (and a spurious bracket(

this is what i've ended up with - which HOORAY i 'think' is working

SELECT AliasSum.FKVendeurID, AliasSum.[SumOfMachines Sold], 
((Select Count(*) FROM qryDistinctSales Where [SumOfMachines Sold]>AliasSum.[SumOfMachines Sold])+1) AS Rank
FROM qrySumofSales AS AliasSum;
 

Can't thank you enough for your unbelieveable patience7

[Rabbit]

Oops, you're right, it was because of the extra bracket and the alias. Good luck with everything else.