Hi everyone,
I'm bulding a query that returns the sales per week and per branche, e.g.: - Week Branch Sales RANKING
-
1st AAA 10 1
-
1st BBB 15 2
-
2nd AAA 5 2
-
2nd BBB 10 1
-
2nd CCC 3 3
THe last column is the one that I want to add: In a module, how can I rank the best branches per week in order to give me the results as shown above?
Thks for your help,
Manuel
6  17399  NeoPa 32,556
Expert Mod 16PB
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 S1.Week = S2.Week
-
AND S1.Sales < S2.Sales
-
GROUP BY S1.Week, S1.Branch
-
ORDER BY S1.Week, S1.Branch
Hi,
Thks 4 ur answer, but it did not work. I wonder if there is any solution in a Module, on which afterwards I could "call" the function. Otherwise, I'm not seeing any other (easy) solution..
Cheers,
Manuel
For this you can use the DCount() function in your query. Let's assume your basic query determines Week, Branch and Sales and is named "qryWeeklySales".
The Dcount would then be: -
select *, Dcount("*","qryWeeklySales","Branch='" & [branch] & "' and Sales<=" & [sales]) As Ranking from qryWeeklySales;
-
This will count the sales being equal or less as the sales from the current record.
This will however make a very slow query...
Nic;o)
For this you can use the DCount() function in your query. Let's assume your basic query determines Week, Branch and Sales and is named "qryWeeklySales".
The Dcount would then be:
-
select *, Dcount("*","qryWeeklySales","Branch='" & [branch] & "' and Sales<=" & [sales]) As Ranking from qryWeeklySales;
-
This will count the sales being equal or less as the sales from the current record.
This will however make a very slow query...
Nic;o)
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.
NeoPa 32,556
Expert Mod 16PB
Hi,
Thks 4 ur answer, but it did not work. I wonder if there is any solution in a Module, on which afterwards I could "call" the function. Otherwise, I'm not seeing any other (easy) solution..
Cheers,
Manuel
A function here would be really messy. Not absolutely impossible but definitely not to be recommended.
If you tell me where my suggestion failed I can look at that further for you. However, as Nico & Rabbit have provided what seem to be workable alternatives already, only do so if you still require my solution.
NeoPa 32,556
Expert Mod 16PB
There were questions posted related to some of the answers here which have been moved to their own separate thread ( Basic SQL Questions). Any interested parties should ensure they are subscribed to the new thread.
Also, bugs were noticed in the SQL from my post (#2) which have now been fixed.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Cady Steldyn |
last post by:
Example:
Date | ItemCode | Stock_In_qty | Stock_Out_qty | Bal_qty
------------------------------------------------------------------
12/09/2003 | A100 | 20 | 0 ...
|
by: PMBragg |
last post by:
Thank you in advance. I'm trying to pull all inventory items from December
of the previous year back to 4 years for my accountant. I know this can be
done, but I'm drawing a blank. I've tried;
...
|
by: PMBragg |
last post by:
Thank you everyone that helped me with my DateSerial question. I really do
appreciate it. Now my accountant has told I need to pull the inventory for
just the year I'm depreciating. Is there a way...
|
by: P Adhia |
last post by:
Hi,
I am trying to understand why following simple query is running very
slow (33 hours)
select ROW_ID
, a.CREATED
, a.CREATED_BY
, a.LAST_UPD
, a.LAST_UPD_BY
|
by: rguarnieri |
last post by:
Hi!
I'm trying to create a query with a boolean expression like this:
select (4 and 1) as Value from Table1
this query return always -1, but when I make the same calculation in
visual...
|
by: Jakob32 |
last post by:
Hi
I'm trying to link information from a query in another Access database to my own Access database using this code (which have worked fine for me earlier).
SELECT *
FROM MinHL IN...
|
by: GladGad |
last post by:
I am not a real well versed query writer and therefore am having some problems figuring out how to write a parameter query where I want to input a partial name. I have tried a few different things,...
|
by: HowHow |
last post by:
First time using crosstab query because tired of creating queries and put them together in one query (such a stupid thing to do :D). Not sure how it works still.
I have link table called...
|
by: lisles |
last post by:
i have a page funtion.php which hs the function to connect to the db
/* Mysql Connection */
function connect(){
global $db_server,$db_user,$db_pass,$db;//Global Values from the config.php...
|
by: Tiddles |
last post by:
Hi
I have a query that ranks some scores, which you kind folks helped me with. However the query isn't quite ranking tied scores as I would like. Instead of 1,2,2,4 it provides 1,3,3,4.
I...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
