1

I am working with Microsoft ACCESS:

I want to create a query, where only the TOP N Profits from another Query are selected. I am using the function "ROW_NUMBER()" which I have seen posted here and in other groups, but when I try to run my SQL, it tells me it is wrong.

Does any of you know, what should I do different?

Here is my SQL-code:

SELECT * from (
    select [Abfrage 13_Rank].[Product 1], 
    [Abfrage 13_Rank].[Product 2],
    [Abfrage 13_Rank].[Product 3],
    [Abfrage 13_Rank].[Country],
    [Abfrage 13_Rank].Profit,
    [Abfrage 13_Rank].ProductsCountry,
    ROW_NUMBER() OVER (PARTITION BY ProductsCountry ORDER BY Profit DESC) AS RankSelect
FROM [Abfrage 13_Rank])
Where RankSelected=[Abfrage 14_Selected].Selected;

Also: My TOP N Profit, is not the same for each of my groups: my N, is different from group to group, and to get the N I am referring to another query ([abfrage 14_Selected]), and that's why I have the last row "Where....."

Thank you very much

Maciej Los
  • 8,468
  • 1
  • 20
  • 35
vale bra
  • 21
  • 4
  • `ROW_NUMBER()` function is not available in MS Access. – Maciej Los Jun 23 '21 at 06:42
  • Aaah, and is there a simila function to ROW_NUMEBR () for Microsoft access? – vale bra Jun 23 '21 at 06:52
  • Yes. Please, see: [Row Number in a Query](https://www.599cd.com/tips/access/140703-row-number/) and [Row_Number() in Access select statement](https://stackoverflow.com/questions/18753368/row-number-in-access-select-statement) – Maciej Los Jun 23 '21 at 06:58
  • Thanks. Whatever I find, has the TOP N where N is the same for each group. I must select different N for each group...do you have any idea? – vale bra Jun 23 '21 at 07:18
  • At this moment - no. Post your input data and expected output then someone will able to help you. Note: do not use images, use text. – Maciej Los Jun 23 '21 at 07:31
  • Query is not using TOP N and N cannot be dynamic. What you want is to pull records where a group sequence number is <= Selected. Tables must have a relationship. Edit question to provide sample data as text tables. Also review http://allenbrowne.com/ranking.html#query – June7 Jun 23 '21 at 08:13
  • I tried this, also does'nt work: SELECT TOP N [Abfrage 13_Rank].[Product 1], [Abfrage 13_Rank].[Product 2], [Abfrage 13_Rank].[Product 3], [Abfrage 13_Rank].Country, [Abfrage 13_Rank].ProductsCountry, [Abfrage 13_Rank].Profit FROM [Abfrage 13_Rank] LEFT JOIN [Abfrage 14_Selected] ON ([Abfrage 13_Rank].Country = [Abfrage 14_Selected].Country) AND ([Abfrage 13_Rank].[Product 3] = [Abfrage 14_Selected].[Product 3]) AND ([Abfrage 13_Rank].[Product 2] = [Abfrage 14_Selected].[Product 2]) AND ([Abfrage 13_Rank].[Product 1] = [Abfrage 14_Selected].[Product 1]) WHERE N= [Abfrage 14_Selected].Selected; – vale bra Jun 23 '21 at 08:23
  • What is `[Abfrage 14_Selected].Selected`? Your query doesn't make sense in any database. – Gordon Linoff Jun 23 '21 at 11:48
  • @GordonLinoff Ja that was wrong. The other code is in comments. Unfortunately, it also does not work. I found out, that TOP N is not a dynamic function...The problem is that I need dynamic – vale bra Jun 23 '21 at 12:17

0 Answers0