TOP Function

[BennyBoy30]

Using the TOP function within an Access query to select a specific number of 'ordered' records from a table.

Rather than selecting the 'Top 10', for example, I wish to set the value, 'x,' as the value of another column in the same table I have. Is there a method of selecting the Top 'x; value this way?
It doesn't seem to be the simple case of assigning 'x' as <<Select Top "table.column_name">>

Hope someone can help me!

Many thanks,

Ben.

[cyberdwarf]

Hi Ben

You'll probably have to construct the query on the fly, by SELECTing the required value into a local variable (SQL) or getting the value using DLOOKUP, DMIN, etc (VBA), then inserting this value into the query string.

HTH

Steve

[BennyBoy30]

Hi Steve,

Thanks for taking a look at this. Unfortunately I don't really know anything about VBA - I'm quite happy writing SQL statements within Access itself but I assume the normal query builder doesn't allow VBA syntax?

Do you know of any link that would show me example syntax on how to declare a DLOOKUP or DMIN and then how to use this value within my sql string?

At present my SQL query is as such:

SELECT TOP 10
FROM Table_AP INNER JOIN [Table_NSR] ON Table_AP.ST_Sector = [Table_NSR].[PSector];

In my 'Table_AP' table there is a column called 'P_Count'.
For each unique PSector in the 'Table_NSR' table I need to select the TOP 'x' amount of records (a distance measurement result). That 'x' is determined by the value in the 'P_Count' column of 'Table_AP'

So if P_Count = 8 then I need to select the TOP 8 records where there is a true join.

The 'Table_NSR' table contains 200 unique PSector records.
The 'Table_AP' table contains 9,000 multiple ST_Sector records.

I need to run this query for all the 200 'Table_NSR' records to provide a multiple 'Top x' number of records for each of the 200 entries.

Hope this explanation helps?

Could you advise which path to take from here? My thoughts are that this doesn't seem to be a too complicated a task but another forum I've used (utteraccess) hasn't helped as yet. But then again my description of the problem might not be great!


Ben.

Hi Ben

You'll probably have to construct the query on the fly, by SELECTing the required value into a local variable (SQL) or getting the value using DLOOKUP, DMIN, etc (VBA), then inserting this value into the query string.

HTH

Steve

[cyberdwarf]

Ben

Basic code example:-

Dim MyVal As Integer
Dim SqlString As String
MyVal = 10
SqlString = "Select Top " & MyVal & " * From MyTable"
DoCmd.RunSql SqlString

HTH

Steve