How to assign a value to a rank - Access 2010

[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/nn235/lisa2802/rmachineres.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;

qryDistinctSale s

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.