469,356 Members | 1,947 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,356 developers. It's quick & easy.

How to assign a value to a rank - Access 2010

30
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
Jan 11 '11 #1
29 8243
Rabbit
12,516 Expert Mod 8TB
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT Salesperson,
  2.      ((SELECT Count(*) FROM tblSales WHERE NumOfSales > x.NumOfSales) + 1) AS Rank
  3. FROM tblSales AS x
Jan 11 '11 #2
Lisa B
30
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?
Jan 11 '11 #3
Rabbit
12,516 Expert Mod 8TB
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.
Jan 11 '11 #4
Lisa B
30
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?
Jan 11 '11 #5
Rabbit
12,516 Expert Mod 8TB
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.
Jan 11 '11 #6
Lisa B
30
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
Jan 15 '11 #7
Lisa B
30
I have uploaded a copy of my dbase to skydrive http://cid-4490afdc094900ec.skydrive...DC094900EC!368
Jan 15 '11 #8
Rabbit
12,516 Expert Mod 8TB
That doesn't match what I posted...

What you want is more like this
Expand|Select|Wrap|Line Numbers
  1. SELECT [VendeurID],
  2.      ((SELECT COUNT(*) FROM [Weekly Machines Sales Data] WHERE [Machines Sold] > x.[Machines Sold])+1) AS Rank
  3. FROM [Weekly Machines Sales data] AS x
Jan 17 '11 #9
Lisa B
30
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
Jan 18 '11 #10
Rabbit
12,516 Expert Mod 8TB
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.
Jan 18 '11 #11
Lisa B
30
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
Jan 18 '11 #12
Rabbit
12,516 Expert Mod 8TB
If you need a ranking grouped by date, you just need to account for that in the subquery's WHERE clause.
Jan 18 '11 #13
Lisa B
30
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
Jan 18 '11 #14
Rabbit
12,516 Expert Mod 8TB
I know. Account for it in the subquery's WHERE clause.
Jan 18 '11 #15
Lisa B
30
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
Jan 18 '11 #16
Rabbit
12,516 Expert Mod 8TB
Put this extra condition in your WHERE clause.
Expand|Select|Wrap|Line Numbers
  1. DateField BETWEEN StartDate AND EndDate
Jan 18 '11 #17
Lisa B
30
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!
Jan 18 '11 #18
Lisa B
30
mispost can' delete
Jan 18 '11 #19
Rabbit
12,516 Expert Mod 8TB
I have no idea what you just said.
Jan 18 '11 #20
Lisa B
30
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
Jan 18 '11 #21
Rabbit
12,516 Expert Mod 8TB
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.
Jan 18 '11 #22
Lisa B
30
It is Summing Dates fine, however i now have the tie problem again :(

Rqrysales
Expand|Select|Wrap|Line Numbers
  1. SELECT Sum([Weekly Machines Sales data].[Machines Sold]) AS [SumOfMachines Sold], [Weekly Machines Sales data].FKVendeurID, [Weekly Machines Sales data].[Date de Vente]
  2. FROM [Weekly Machines Sales data]
  3. GROUP BY [Weekly Machines Sales data].FKVendeurID, [Weekly Machines Sales data].[Date de Vente]
  4. HAVING ((([Weekly Machines Sales data].[Date de Vente]) Between [forms]![RankMachines]![StartDate] And [Forms]![RankMachines]![EndDate]))
  5. ORDER BY Sum([Weekly Machines Sales data].[Machines Sold]) DESC;
  6.  
RankingMachines

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM Rqrysales AS SumMach1
  3. WHERE (((SumMach1.[Date de Vente]) Between [forms]![RankMachines]![StartDate] And [Forms]![RankMachines]![EndDate]))
  4. GROUP BY SumMach1.[SumOfMachines Sold], SumMach1.FKVendeurID;
  5.  
Distinct
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT [Weekly Machines Sales data].FKVendeurID
  2. FROM [Weekly Machines Sales data]
  3. WHERE ((([Weekly Machines Sales data].[Date de Vente]) Between [forms]![RankMachines]![StartDate] And [forms]![RankMachines]![EndDate]))
  4. GROUP BY [Weekly Machines Sales data].FKVendeurID;
  5.  
Jan 19 '11 #23
Rabbit
12,516 Expert Mod 8TB
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.
Jan 19 '11 #24
Lisa B
30
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
Jan 19 '11 #25
Rabbit
12,516 Expert Mod 8TB
You'll need to return just the distinct dollar amounts for the ranking subquery to remove the duplication.
Jan 19 '11 #26
Lisa B
30
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
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT [Weekly Machines Sales data].FKVendeurID, Sum([Weekly Machines Sales data].[Machines Sold]) AS [SumOfMachines Sold]
  2. FROM [Weekly Machines Sales data]
  3. WHERE ((([Weekly Machines Sales data].[Date de Vente]) Between [forms]![RankMachines]![StartDate] And [forms]![RankMachines]![EndDate]))
  4. GROUP BY [Weekly Machines Sales data].FKVendeurID;
  5.  
RqrySales
Expand|Select|Wrap|Line Numbers
  1. SELECT Sum([Weekly Machines Sales data].[Machines Sold]) AS [SumOfMachines Sold], [Weekly Machines Sales data].FKVendeurID, [Weekly Machines Sales data].[Date de Vente]
  2. FROM [Weekly Machines Sales data]
  3. GROUP BY [Weekly Machines Sales data].FKVendeurID, [Weekly Machines Sales data].[Date de Vente]
  4. HAVING ((([Weekly Machines Sales data].[Date de Vente]) Between [forms]![RankMachines]![StartDate] And [Forms]![RankMachines]![EndDate]))
  5. ORDER BY Sum([Weekly Machines Sales data].[Machines Sold]) DESC;
  6.  
RankingMachines
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM Rqrysales AS SumMach1
  3. WHERE (((SumMach1.[Date de Vente]) Between [forms]![RankMachines]![StartDate] And [Forms]![RankMachines]![EndDate]))
  4. GROUP BY SumMach1.[SumOfMachines Sold], SumMach1.FKVendeurID;
  5.  
Jan 19 '11 #27
Rabbit
12,516 Expert Mod 8TB
Wow, your SQL is a mess, I'm having trouble understanding all of it.

Try this:

qrySumOfSales
Expand|Select|Wrap|Line Numbers
  1. SELECT [Weekly Machines Sales data].FKVendeurID, Sum([Weekly Machines Sales data].[Machines Sold]) AS [SumOfMachines Sold]
  2. FROM [Weekly Machines Sales data]
  3. WHERE ((([Weekly Machines Sales data].[Date de Vente]) Between [forms]![RankMachines]![StartDate] And [forms]![RankMachines]![EndDate]))
  4. GROUP BY [Weekly Machines Sales data].FKVendeurID;
qryDistinctSales
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT qrySumOfSales.[SumOfMachines Sold]
  2. FROM qryDistinctSales
qryRankSales
Expand|Select|Wrap|Line Numbers
  1. SELECT qrySumOfSales.FKVendeurID, qrySumOfSales.[SumOfMachines Sold],
  2. ((SELECT Count(*) FROM qryDistinctSales WHERE [SumOfMachines Sold] > x.[SumOfMachines Sold]]) + 1) AS Rank
  3. FROM qrySumOfSales AS x
Jan 20 '11 #28
Lisa B
30
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
Expand|Select|Wrap|Line Numbers
  1. SELECT AliasSum.FKVendeurID, AliasSum.[SumOfMachines Sold], 
  2. ((Select Count(*) FROM qryDistinctSales Where [SumOfMachines Sold]>AliasSum.[SumOfMachines Sold])+1) AS Rank
  3. FROM qrySumofSales AS AliasSum;
  4.  
Can't thank you enough for your unbelieveable patience7
Jan 20 '11 #29
Rabbit
12,516 Expert Mod 8TB
Oops, you're right, it was because of the extra bracket and the alias. Good luck with everything else.
Jan 20 '11 #30

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Sergei | last post: by
1 post views Thread by harpreet1433 | last post: by
1 post views Thread by No_Spam | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.