473,326 Members | 2,113 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Parameterizing top N in query MS-access 2007

Hi,
There are many occasions where my Boss would want to print a top values report for different cases.
I have been googling for how to enter the TOP N eg TOP 100, or TOP 200 in a parameter query. Having to do it manually by opening the SQL view in MS-Access 2007 to change the value of N, would be inconvenient if the user does not know the way around the basics of SQL view.
I would like to find out if it is possible to design a parameter query that opens up a dialog for you to input the value of N would make it more user friendly.

A sample sql would clearly show the problem.

Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 50 Sum(tbl_Inv.Amount) AS SumOfAmount, tbl_Inv.Month, tbl_Inv.Year
  2. FROM tbl_Inv
  3. GROUP BY tbl_Inv.Month, tbl_Inv.Year
  4. ORDER BY Sum(tbl_Inv.Amount) DESC;
  5.  
I would be very grateful to hear from you soon.
Thanks.

BenGik
Oct 19 '16 #1

✓ answered by jforbes

I'm pretty sure that TOP can not be parameterized.

You could create a Form that asks for the Top Amount and then creates the SQL needed. Then depending on what you want to display to your users, you could have it update a QueryDef or set the RecordSource of a Report or Form to that of the SQL that was created, then display the results to the User.

There are other options as well, like displaying a Form to get the Top Amount, then opens a Report and passes the Top Amount as an OpenArg so that when the Report Opens, it updates it's own RecordSource to use the OpenArg. Or you can have the Report open the Form to get the Top Amount, but I would recommend against it.

Here is a really bad example of what can be done in the Open Event of a Report:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2.     Dim iTop As Variant
  3.     Dim sSQL As String
  4.     iTop = Val(InputBox("How many records would you like?"))
  5.     If iTop > 0 Then
  6.         sSQL = "SELECT TOP " & iTop & " * FROM Invoice"
  7.     Else
  8.         sSQL = "Invoice"
  9.     End If
  10.     Me.RecordSource = sSQL
  11. End Sub
The point is that you can create SQL in code and then supply it to whatever Output you like.

6 963
jforbes
1,107 Expert 1GB
I'm pretty sure that TOP can not be parameterized.

You could create a Form that asks for the Top Amount and then creates the SQL needed. Then depending on what you want to display to your users, you could have it update a QueryDef or set the RecordSource of a Report or Form to that of the SQL that was created, then display the results to the User.

There are other options as well, like displaying a Form to get the Top Amount, then opens a Report and passes the Top Amount as an OpenArg so that when the Report Opens, it updates it's own RecordSource to use the OpenArg. Or you can have the Report open the Form to get the Top Amount, but I would recommend against it.

Here is a really bad example of what can be done in the Open Event of a Report:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2.     Dim iTop As Variant
  3.     Dim sSQL As String
  4.     iTop = Val(InputBox("How many records would you like?"))
  5.     If iTop > 0 Then
  6.         sSQL = "SELECT TOP " & iTop & " * FROM Invoice"
  7.     Else
  8.         sSQL = "Invoice"
  9.     End If
  10.     Me.RecordSource = sSQL
  11. End Sub
The point is that you can create SQL in code and then supply it to whatever Output you like.
Oct 20 '16 #2
Hi,
Thanks very much for your clever solution for I have been searching this for ages.
One final question that follows from the "Top N" question is:
How do I pass the value "N" I entered to also appear in the heading of the report such that if N changes, the value also changes in the heading like that?
I could not figure out the method to solve it.
With appreciation.

BenGik
Oct 20 '16 #3
jforbes
1,107 Expert 1GB
Again, there are a lot of options for this sort of thing, but I would put it in either a Global Variable, TempVar, or Report Variable based on where the "N" value is captured from the user.

Here is another bad example (based on the previous bad example) on how to capture it on a Report:
Expand|Select|Wrap|Line Numbers
  1. Private iTop As Variant
  2. Private Function getTopAmount() As Variant
  3.     getTopAmount = iTop
  4. End Function
  5. Private Sub Report_Open(Cancel As Integer)
  6.     'Dim iTop As Variant
  7.     Dim sSQL As String
  8.     iTop = Val(InputBox("How many records would you like?"))
  9.     If iTop > 0 Then
  10.         sSQL = "SELECT TOP " & iTop & " * FROM Invoice"
  11.     Else
  12.         sSQL = "Invoice"
  13.     End If
  14.     Me.RecordSource = sSQL
  15. End Sub

Then a TextBox in a Report Header could have the following as a Control Source:
Expand|Select|Wrap|Line Numbers
  1. ="Top Amount:  " & getTopAmount()
Oct 20 '16 #4
Hi Sir,
Thanks so much for solving my "Parameterizing top N in query MS-access 2007" last week.
But you really confused me by saying the solution is a very bad one!
Could you give me an outline as to how that which is working good is labelled a bad answer? Just a synopsis.
Thanks.

BenGik
Oct 24 '16 #5
jforbes
1,107 Expert 1GB
The idea of setting the RecordSource on open works well.

I was referring to the Examples as being bad. The examples work to explain the concept, but they could be drastically improved upon, especially the use of an InputBox.
Oct 25 '16 #6
Hi,
Thanks for your time, sir. You are really an Access champ!
Its been an eye opener to understanding your methods of solution.
If you were a chess player you could be a champion!
Thanks.
BenGik
Oct 25 '16 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Siegfried Heintze | last post by:
I have some cygwin cron jobs running under the Administrator account populating a MSAccess database that is simultaneously being queried by IIS/ASP.NET/C#. If I manually use windows explorer to...
2
pentahari
by: pentahari | last post by:
i create the switchboard menu using access 2007. How to convert this access file to executable file.
5
by: tasawer | last post by:
I need some assistance with SQL query. I will be grateful if you can provide help please. I have a database that records details of a road traffic accident and name of driver. sub-form records...
1
by: priyamtheone | last post by:
Hi, I have a few questions. First of all, what's the flexibility and strength of MSAccess 2007 and how much data and transactions can it support? Second, I would like to know whether it's optimum...
1
by: Raphael Eshun | last post by:
Hi folks! Am new in asp. I have already developed a database in MsAccess-2007 and connected it to my vb6 form. But I want a remote user to have access to the database using an asp web page. Pls,...
1
by: zibute | last post by:
I have created an msACCESS database with a report and subqueries. As I am developing it I am able to print to a printer. I have now copied the database and the new database does not print -- Says...
7
by: Brian Woods | last post by:
Im new to Upsert queries in Access and I have run into a problem. I have two tables, tblParticipants and tblAssignments. What I need is a query that will perform an upsert from tblParticipants to...
3
by: Marc Haazen | last post by:
Hi all, I developed an application where within one main form 3 subforms are used. 1. Documents, 2. Items within a document, 3. optional Table data if the item represents a table type item. All...
5
by: Christine T | last post by:
I have developed an MS Access Application that has many users on different versions of Office. It has custom menus that are built via vba code every time it is started. The menus and their contents...
1
by: baba | last post by:
Can I change Month(Now) in Msaccess to point to someother month without changing the system date in the taskbar date/time settings of my computer. In otherwords,is there a way to change the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.