0

I find very similar questions to mine, but noone contains my problem.

I work with ms-Acces: I need to create a Rank query of my groups, selecting the TOP N Profits for each group.

THE PROBLEM is that this "N" is not the same for each group, but I need to select every time different Ns...

So the usual TOP N function does not work for me...and COUNT does not work either. How can I rank pro group the TOP N=1 or 2 or 3 etc. according to a selection pro group, made in a previous query ([Abfrage 14_Selected])?

Here one example that does not (clearly) work, but I don´t know how to solve my problem:

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;

Thanks everyone

vale bra
  • 21
  • 4

2 Answers2

1

I found the solution myself, and I decided to post it here because it might help someone else in the future.

So here, is the right code to use when:

  1. you want to select the TOP N ranks pro group from a table with many rows, and
  2. how many N pro groups should be selected was defined in a previous query.

SELECT [Abfrage 12_Rank].[Product 1], [Abfrage 12_Rank].[Product 2], [Abfrage 12_Rank].[Product 3], [Abfrage 12_Rank].ProductCountry, [Abfrage 12_Rank].Country, [Abfrage 12_Rank].Rank FROM [Abfrage 12_Rank] LEFT JOIN [Abfrage 13_Selected] ON ([Abfrage 12_Rank].[Product 1] = [Abfrage 13_Selected].[Product 1]) AND ([Abfrage 12_Rank].[Product 2] = [Abfrage 13_Selected].[Product 2]) AND ([Abfrage 12_Rank].[Product 3] = [Abfrage 13_Selected].[Product 3]) AND ([Abfrage 12_Rank].Country = [Abfrage 13_Selected].Country) WHERE ((([Abfrage 12_Rank].Rank)<=[Selected])) ORDER BY [Abfrage 12_Rank].ProductCountry, [Abfrage 12_Rank].Profit DESC;

vale bra
  • 21
  • 4
0

You can't. Neither Top N nor table and field names can be dynamic.

So, you must create (or adjust) the parts or the final SQL from code (VBA) before running the query.

You can do that by creating a QueryDef object and assign the final SQL to its property SQL.

Gustav
  • 53,498
  • 7
  • 29
  • 55