CODEWITHSUNDEEP
sqlms-access
rosuandreimihai
rosuandreimihai

Reputation: 656

Microsoft Access Query

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

Answers (3)

Parfait
Parfait

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

rosuandreimihai
rosuandreimihai

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

Gustav
Gustav

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

Related Questions