Select Top different number of records

[mshakeelattari]

I have a table:

ChapID   QID    (other fields)
1        1
1        2
1        3
1        4
1        5
2        1
2        2
2        3
3        1
3        2
--       --

I am able to select Top 2 records for each of ChapID using query

SELECT t.ChapID, t.QID, FROM MyTable AS t
WHERE (((t.QID) In (SELECT TOP 2 QID FROM MyTable WHERE ChapID = t.ChapID )))
ORDER BY t.ChapID, t.QID;

I am also able to change the Top 2 with any number uing "QueryDef" method.
Now the requirement is that I should be able to use a text box on a form against each ChapID to get a number and my query should return that number of top records (different for each ChapID) for each Chap ID.
One of my ideas include to add another table linked to this one with ChapID including a field NoOfRecs, like:

ChapID     NoOfRecs
1           3
2           2
3           1

and a query should pick this number to show top x number of records.
Any help please? asap.

[gnawoncents]

When you say:

I should be able to use a text box on a form against each ChapID to get a number and my query should return that number of top records (different for each ChapID) for each Chap ID.

...are you saying that the user will input/select the ChapID?

Also, is there any reason the NoOfRecs couldn't go on the same original table? That would make it a little simpler. Either way, you could modify your query using a dlookup to find the number of records based on the selected ChapID.

I get the feeling though that there is something here I'm missing. Could you explain in a little more detail how it works from selecting the chapter to displaying the results?

[mshakeelattari]

Thank you for your quick response.

The requirement is to get x number of QIDs for each ChapID.
For example:
for ChapID=1, I need Three QIDs (in MyTable there are five QIDs against ChapID=1); for ChapID=2 (Total QIDs for ChaptID=2 are three), two QIDs are required,
for ChapID=3, only one out of two QIDs is needed in a query or on a report.

The number of QIDs required from each ChapID may be taken from a Textbox on a Form shown against each chapID in a list or it may be pre-entered into the original or different Table.

It is actually Different number of questions required from different chapters.

Thanking in anticipation.

[gnawoncents]

So, just to make sure I understand, the user selects a ChapID and you want the query to return the top X records from MyTable where X = the NoOfRecs that correspond to ChapID, regardless of QID.

If so, and you are already using QueryDef to update the "Top X" records, all you need to do is replace X with a Dlookup to find the number of records you want to return. If you haven't already built all this, there are other options that might be easier to implement. Otherwise, this should just plug into your existing code.

DLookup("[NoOfRecs]", "YourNewTable", "[ChapID] = " & Me.YourFormField)
 

YourNewTable = the table that stores NoOfRecs for each ChapID
YourFormField = the name of the field on your form where you enter the chapter number (the "Me." assumes of course that you are running the code from that same form)

[Rabbit]

Is QID always sequential starting from 1 for any chapter? If so, you can just filter on that using a less than or equal to the number of recs

[mshakeelattari]

thank you @gnawoncents . you said:

, there are other options that might be easier to implement.

Please just point out those ones. Thank you.

[gnawoncents]

@Rabbit already pointed out one option, which will work if, as already stated, the QID is "always sequential starting from 1 for any chapter."

In order to find the best solution, however, we need some more information on how the results will be used.
- Will the results be displayed immediately to a form/report? If so, you could just create/modify the form's/report's filter.
- Will the results be used to do further work and not necessarily displayed immediately? If so, you are better off creating and modifying a query or a recordset for more temporary use.

Again, if you can give us a better picture of how you intend to use the results, we can do more to guide you to a good answer.