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.