Question Ranking

[loopyl00]

I have been trying for agest to get a ranking expression that works

I have a sales table [weekly sales data] where each [vendeur] has sold [machines sold] and [accounts open]

I want to rank who has sold the most and in a seperate ranking who has opened the most accounts - and by date

I also then want to assign a value for the ranks - 1st - 6, 2nd 4, 3rd 3, 4th 4, 5th 5

I have been setting up queries all over the place and using dklookup but just get errors

tehre is a copy of my dbase attached
any help gratefully received

Very grateful for any help

 

[John Big Booty]

Welcome to the forum.

Perhaps the sample posted here will give you some pointers.

 

[loopyl00]

Wow that is EXACTLY what i want, i'm so close I can taste it

 

[vbaInet]

Wow that is EXACTLY what i want, i'm so close I can taste it

What does it taste? Pure goodness?

I haven't looked at your db but I would imagine you need to add another criteria to link it to the current record's ID.

 

[loopyl00]

At 2 am and after many hrs of heartache it tastes fantastic, too wired to sleep, slight problem now though that the form is ranking as per all data not as per the data select in the Date field -!

Ideally a Date Range would be better here and i wish i could lose the combo box field formatting on the form

new file attached

Query r1qry
Query Rank - Machines
Form RankMachines

Need to pick dates e.g. 07/01/2011 or 14/01/2011

 

[John Big Booty]

Sorry I don't have Access '10. Any chance you can save it back to '07 or better still '03.

 

[John Big Booty]

You might also want to consider implementing a naming protocol, something like; TBL_TableName, FRM_FormName, QRY_QueryName, RPT_ReportName. Limit yourself to alpha numeric characters and under score (_), avoid spaces and all other special characters.

I can't open the tables so have no idea if they are properly normalised.

 

[loopyl00]

I think i've figured it out - it's if there is a tie 0- how do i edit the code so that if two people have the same rank, it just carries on counting

i.e.

1
2
3
3
4
5
5
5
6
6

 

[John Big Booty]

I've just tested in my sample and it does just that

 

[loopyl00]

i have used you're sample data here and experience the same problem

see attached

is screenshare on skype poss?

 

[loopyl00]

Tried and failed to get my head around this support.microsoft.com/kb/208946

 

[John Big Booty]

i have used you're sample data here and experience the same problem

see attached

is screenshare on skype poss?

Sorry, I must have misunderstood you.

If you want to break a tie you will need to define how the tie is decided and then implement a secondary ranking system to rank from there.

 

[loopyl00]

I have it working successfully - yay

Rank: (Select Count(*) from DistinctCount Where [CountofMachines Sold] >[Prod1].[CountofMachines sold])+1

i created a second query DistinctCount where it has a unique value

however as soon as i enter any date criteria i am back to the original problem

I'm using criteria in the Date field to pull the date range from a form
Between [Forms]![RankMachines]![Start Date] And [Forms]![RankMachines]![EndDate]

do i need to add the date field into one of the earlier queries that define the data for my final ranking?

 

[loopyl00]

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

So i tried to adapt it

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: (Select count(*) from [Distinct]
Where [SumOfMachines Sold] > [SumMach1].[SumOfMachines Sold])+1

and it would seem now it is not starting the ranking at 1 if there is a higher sales value before the chosen date 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