0

I have a simple Access table [Parts] that stores the data 

Part#  CYTD_SALES    CUST_NUM

I need to rank the parts in terms of CYTD Sales (Current Year To Day Sales) for each CUST_NUM (Customer Number) separately

I am using the following statement:

Rank: 

(SELECT Count (*)  
 FROM [Parts] as R 
 Where [CYTD Sales] > [Parts].[CYTD Sales] 
   And > CUST_NUM= [Parts].[CUST_NUM] ) + 1

I ran into situation when the same amount was spent for more than 1 part by same customer, so the ranking looks like this 

1 2 4 4 5

instead of 

1 2 3 4 5

Is there a way to write a statement that would not allow identical rankings for the same customer?

Thank you!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Aquarelle
  • 23
  • 1
  • 5
  • Is there no primary key in the [Parts] table? – Joachim Isaksson Apr 05 '14 at 16:27
  • No, this table is an output of another query. Thank you! – Aquarelle Apr 05 '14 at 16:31
  • If you don't have any field by which you can tell the rows apart, I know of no way to rank the rows uniquely. Perhaps you could have the previous query output some kind of unique identifier? – Joachim Isaksson Apr 05 '14 at 16:33
  • i think i could do a combo field that would be identical, each part is only listed once for each customer, so a field that would contain "Part#&CUST_NUM" would be unique. Would that work? – Aquarelle Apr 05 '14 at 16:40

1 Answers1

0

Since Part# field is unique for each customer, below is the statement that worked for me: 

(SELECT Count (*)  
 FROM [Parts] as R 
 Where ([CYTD Sales] > [Parts].[CYTD Sales] Or 
([CYTD Sales]=[Parts].[CYTD Sales] And
 [Part#]> [Parts].[Part#]))
   And > CUST_NUM= [Parts].[CUST_NUM] ) + 1
Aquarelle
  • 23
  • 1
  • 5