Reputation: 656
I have one settings table that is storing different setting keys for a app built using Microsoft Access One of the settings key drives how many records should be seen in a dropdown list The query behind the list is similar to the one below:
Select Top 3 id, name FROM tblRegular
Now, I want to achieve something like this:
Select Top (Select keyValue FROM tblSettings WHERE key="rowNumber") id, name FROM tblRegular
But, using it like this does not work as it fires errors Could someone tell me if it can be done?
EDIT: The table structure looks similar to the one below: tblRegular:
id | name
1 'A'
2 'B'
3 'C'
tblSettings:
id | key | keyValue
1 'rowNumber' 2
Thank you!
Upvotes: 0
Views: 280
Reputation: 107767
Consider the pure SQL solution using a correlated subquery to calculate a rowCount that is then used in outer query to filter by number of rows:
SELECT main.id, main.[name]
FROM
(SELECT t.id, t.[name],
(SELECT Count(*) FROM tblRegular sub
WHERE sub.[name] <= t.[name]) AS rowCount
FROM tblRegular t) AS main
WHERE main.rowCount <= (SELECT Max(s.keyValue) FROM tblSettings s
WHERE s.key = 'rowNumber')
Alternatively with the domain aggregate, DMax()
:
SELECT main.id, main.[name]
FROM
(SELECT t.id, t.[name],
(SELECT Count(*) FROM tblRegular sub
WHERE sub.[name] <= t.[name]) AS rowCount
FROM tblRegular t) AS main
WHERE main.rowCount <= DMax("keyValue", "tblSettings", "key = 'rowNumber'")
Upvotes: 1
Reputation: 656
Ok, so, just found out that using the Select statement as it was in the question would have triggered the errors mentioned. So, the approach that worked is to alter the .RowSource of the dropdown on form load and the query placed in the rowsource should look like:
Select Top (" & rowNr & ") id, name FROM tblRegular
where rowNr
is fetched using another SQL query or even a DAO/ADO function to retrieve the value from the database
Upvotes: 0
Reputation: 56016
This syntax does fail in Access SQL, barking at "Select" with the (localised) message like:
The SELECT sentence contains a reserved word or argument, that is misspelled or missing, or the punctuation is not correct.
Upvotes: 0