select top 5

  • Thread starter Thread starter Souris
  • Start date Start date
S

Souris

I would like to configurable top records in my query.

Does MS Access 2003 support dlookup in the query for select top query?

For example

Select top Dlookup("[MyNumberField]", "MyTable", "[MyId] = 1") MyField1,
Mifield2 from MyTabl1

Will the number return records depends on MyNumberField in MyTable?

Your information is great appreciated,
 
You cannot put that in a saved query and expect Access to return a variable
number of rows.

You could however, build a SQL string and save that in the SQL property of a
query. Put this code in an event procedure (command button click or
something like that).

Dim strSQL as string

strSQL = "SELECT TOP " & DLOOKUP("MyNumberField", "MyTable", "MyID = 1") _
& " MyField2 FROM Table1 "
Currentdb.querydefs("QueryName").sql = strsql
Docmd.openquery "QueryName"


HTH
Dale
 
Actually, if you want exactly 5 records, you need to add a Order By clause
that sorts on some field in your database. Access will return the top 5 and
any matches in the Order By clause, so if you want exactly 5, I generally do
something like:

ORDER BY [DateField], Rnd([ID])

HTH
Dale
 
Thanks millions,

Dale Fye said:
Actually, if you want exactly 5 records, you need to add a Order By clause
that sorts on some field in your database. Access will return the top 5 and
any matches in the Order By clause, so if you want exactly 5, I generally do
something like:

ORDER BY [DateField], Rnd([ID])

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Souris said:
I would like to configurable top records in my query.

Does MS Access 2003 support dlookup in the query for select top query?

For example

Select top Dlookup("[MyNumberField]", "MyTable", "[MyId] = 1") MyField1,
Mifield2 from MyTabl1

Will the number return records depends on MyNumberField in MyTable?

Your information is great appreciated,
Click to expand...
Click to expand...
 
One method that can be used is to use a ranking query to assign a number to
the records and then use the ranking to return N records.

'Probably not updatable
SELECT MyTable.*
FROM MyTable INNER JOIN
(
SELECT A.MyField, Count(B.MyField) as TheCount
FROM MyTable as A LEFT JOIN MyTable As B
ON A.MyField < B.MyField
GROUP BY A.MyField
) as Ranking
ON MyTable.MyField = Ranking.MyField
WHERE Ranking.TheCount < [Top How Many Number]

'Probably updatable
SELECT MyTable.*
FROM MyTable
WHERE MyField in
(SELECT A.MyField
FROM MyTable as A LEFT JOIN MyTable As B
ON A.MyField < B.MyField
GROUP BY A.MyField
HAVING Count(B.MyField) < [Top How Many Number])

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top