Rowlimited Query on Web Database

[GrandMasterTuck]

Hey there hi there ho there...

I have an Access web-database I'm working on for assigning students to classrooms based upon their choices. I have a table of classrooms and the max number of students per classroom, a table of students, and a table of requests. Students can make more than one request for a classroom.

What I want to do is have a query that shows a list of students that requested a classroom, but I only want to show a maximum number of records that matches that classroom's max number. So if I have thirty kids that requested Classroom1, and Classroom1 has a max of 15 in the Classrooms table, I want the query to show me the top 15 kids only (don't worry about how they're sorted).

The question is, how do I do this in an Access Web Database? It's a breeze with a regular database, but this is going to go on a website so all the teachers and students can see it. I fiddled a bit with the database and got the form working right, but I can't get it to only show x number of records. It always shows all of them.

Anyone got any ideas? Thanks a bunch!

 

[sneuberg]

The SELECT TOP N statement selects N rows if there are more rows and however many there are if there are less. So I think you could build your querydef and concatenate the N into it.

 

[GrandMasterTuck]

So I think you could build your querydef and concatenate the N into it.

I would have no idea where to begin with the syntax of building the querydef like this. Any suggestions?

 

[sneuberg]

Build your query with the query designer then switch to SQL view and insert

TOP 15

after the SELECT keyword.