Basic SQL Questions

[cargo]

Assuming a recordsource [tblSales] with fields [Week]; [Branch] & [Sales], you could try :

SELECT S1.Week, S1.Branch, S1.Sales, Count(S2.Week) + 1 AS [Ranking]
FROM [tblSales] AS [S1] LEFT JOIN [tblSales] AS [S2]
  ON tblSales.Week = tblSales2.Week
 AND tblSales.Sales < tblSales2.Sales
GROUP BY S1.Week, S1.Branch
ORDER BY S1.Week, S1.Branch

Hello.
I´m a newer newbie to Access. (and SQL)
What is S1 and S2? Are these tables?
You also write about tblSales2?
Are these 3(?) tables to be created before the execution of this query?

[cargo]

This will be faster:

SELECT *, ((SELECT Count(*) FROM qryWeeklySales WHERE Branch = x.Branch AND Sales > x.Sales;)+1) AS Rank
FROM qryWeeklySales AS x;
 

You need to use greater than if you're trying to rank by descending sales.

Hello,
what is the meaning of x? I guess
[tblSales] with fields [Week]; [Branch] & [Sales] ?

As I understand it´s assumed there already is a query called
qryWeekSales - how would that look like?
Do you mean it´s a query that creates a table (tblSales) that look like this
Week Branch Sales
1st AAA 10
1st BBB 15
2nd AAA 5
2nd BBB 10
2nd CCC 3

Thank you!

[NeoPa]

I'll try to answer your questions but please don't use someone else's thread to ask your own questions in future. This is called thread hi-jacking and is considered bad etiquette (as well as being against the rules of this site).
This thread has been split away from How to rank a query in MS Access?.
Before I can start though there is some work I still need to do to tidy up this situation.

ADMIN.

[NeoPa]

Hello.
I´m a newer newbie to Access. (and SQL)
What is S1 and S2? Are these tables?
You also write about tblSales2?
Are these 3(?) tables to be created before the execution of this query?

Some good questions. Good because they drew my attention to some bugs (shame) in my posted code. The code is now fixed (you can follow the link to see the new version).

Anyway, to your questions.
S1 & S2 are SQL Aliases.
tblSales2 was a mistake and should have been S2.
In a query such as this, the same table needs to be included twice so at least one of them MUST have an alias so SQL knows which is which. Aliases can also be used for other reasons and, as in this case, they can make more complicated SQL much more readable.

[cargo]

Anyway, to your questions.

S1 & S2 are SQL Aliases.
tblSales2 was a mistake and should have been S2.
In a query such as this, the same table needs to be included twice so at least one of them MUST have an alias so SQL knows which is which. Aliases can also be used for other reasons and, as in this case, they can make more complicated SQL much more readable.

Thank you very much for your help!
/cargo

[NeoPa]

No problems. It's always good to get some straightforward and fundamental questions that can be answered as fact rather than as opinions or style which can complicate matters. There are certainly others out there who need this question answered too :)