split table/query

  • Thread starter Thread starter Mario
  • Start date Start date
M

Mario

I have a table

Name Age
Name1 15
Name2 35
Name3 27
.. .
.. .
.. .
Name9 20

I would like to create a report (statistics by age)
that would calculate only first 5 (or 7 or 149) records.

(How to create a query with first n records?)
 
Mario,

Make a simple Select query, specify the sorting order and then right-click
in the table area of the design view, select propertiesand enter the number
you wish in the Top Values property.

HTH,
Nikos
 
Mario,

OK then, my suggestion is: make a select query to collect, filter and sort
your data as desired, and specify any number of Top Values. Then at runtime
you can change that number in the query definition before you open the
report.
While in query design, change to SQL view and copy the SQL statement to
paste in your code below, and replace the top values number with a variable
reading the desired number from the form:

Then at run time change the number of top values through code and open the
report, through some code like:

Dim qbf As QueryDef
Dim topvalues as Long
Dim strSQL as String
topvalues = Forms!FormName.txtBoxName
strSQL = "SELECT TOP " & topvalues & " field_name1, field_name2 FROM
Table_Name WHERE ..... ORDER BY ...."
Set qbf = CurrentDb.QueryDefs("QueryName")
qbf.SQL = strSQL
DoCmd.OpenReport "ReportName", acViewPreview '(or acViewNormal to print)

You will need to change the query, form report and text box name to the
actual ones. Also, you wil need to add the Microsoft DAO Object Library
reference if not already there (Tools > References from the VBA window, DAO
3.51 for Access 97, DAO 3.6 for Access 2000 or later).

HTH,
Nikos
 
Nikos Yannacopoulos said:
Mario,

OK then, my suggestion is: make a select query to collect, filter and sort
your data as desired, and specify any number of Top Values. Then at runtime
you can change that number in the query definition before you open the
report.
While in query design, change to SQL view and copy the SQL statement to
paste in your code below, and replace the top values number with a variable
reading the desired number from the form:

Then at run time change the number of top values through code and open the
report, through some code like:

Dim qbf As QueryDef
Dim topvalues as Long
Dim strSQL as String
topvalues = Forms!FormName.txtBoxName
strSQL = "SELECT TOP " & topvalues & " field_name1, field_name2 FROM
Table_Name WHERE ..... ORDER BY ...."
Set qbf = CurrentDb.QueryDefs("QueryName")
qbf.SQL = strSQL
DoCmd.OpenReport "ReportName", acViewPreview '(or acViewNormal to print)

You will need to change the query, form report and text box name to the
actual ones. Also, you wil need to add the Microsoft DAO Object Library
reference if not already there (Tools > References from the VBA window, DAO
3.51 for Access 97, DAO 3.6 for Access 2000 or later).

HTH,
Nikos
Click to expand...
 
Back
Top