By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,221 Members | 1,121 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,221 IT Pros & Developers. It's quick & easy.

Select Top different number of records

P: 90
I have a table:
Expand|Select|Wrap|Line Numbers
  1. ChapID   QID    (other fields)
  2. 1        1
  3. 1        2
  4. 1        3
  5. 1        4
  6. 1        5
  7. 2        1
  8. 2        2
  9. 2        3
  10. 3        1
  11. 3        2
  12. --       --
I am able to select Top 2 records for each of ChapID using query
Expand|Select|Wrap|Line Numbers
  1. SELECT t.ChapID, t.QID, FROM MyTable AS t
  2. WHERE (((t.QID) In (SELECT TOP 2 QID FROM MyTable WHERE ChapID = t.ChapID )))
  3. 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:
Expand|Select|Wrap|Line Numbers
  1. ChapID     NoOfRecs
  2. 1           3
  3. 2           2
  4. 3           1
and a query should pick this number to show top x number of records.
Any help please? asap.
Feb 20 '18 #1
Share this Question
Share on Google+
6 Replies

P: 214
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?
Feb 20 '18 #2

P: 90
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.
Feb 20 '18 #3

P: 214
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.
Expand|Select|Wrap|Line Numbers
  1. 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)
Feb 20 '18 #4

Expert Mod 10K+
P: 12,357
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
Feb 20 '18 #5

P: 90
thank you @gnawoncents . you said:
Expand|Select|Wrap|Line Numbers
  1. , there are other options that might be easier to implement.

Please just point out those ones. Thank you.
Feb 20 '18 #6

P: 214
@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.
Feb 22 '18 #7

Post your reply

Sign in to post your reply or Sign up for a free account.