Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2011
    Posts
    18

    Unanswered: Selecting variable number of records based on table field value

    Hello all, I have an issue I have not been able to figure out. I am trying to select a variable number of records based on a table field. For example, I have a table called Dealers with a primary key field called ID with the values 1 and 2. The table has another field, let's call it Count. ID 1 has a value of 21 in the Count field, ID 2 has a Count field value of 33. I have a separate table called Leads. This table also contains an ID field with 40 records with an ID of 1, and 50 records with an ID of 2.

    I'm wondering if it is possible to write a query that can select a certain number of records from the Leads table based on the the Count field value from table Dealers. In the example above, I would want to select 21 records for ID 1 and 33 records from ID 2.

    Does anyone have any experience with this? I cannot figure out a way to select a variable number of fields with Top Values. I'm wondering if this would have to be done via VBA? I don't have a lot of experience in that area, can anyone provide assistance? Any help is greatly appreciated. Thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you need two queries
    the first finds the number of rows to return, the id field

    the second actually goes and gets those rows using the value of the id field as the top n

    strSQL ="select ID from dealers"

    strSQL1 = select top (" & myfirstrecordset!id & ").....

    why you need to specify the number of rows mystifies me though
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2011
    Posts
    18
    Quote Originally Posted by healdem View Post
    so you need two queries
    the first finds the number of rows to return, the id field

    the second actually goes and gets those rows using the value of the id field as the top n

    strSQL ="select ID from dealers"

    strSQL1 = select top (" & myfirstrecordset!id & ").....

    why you need to specify the number of rows mystifies me though
    Haha, it's a business needs deal

    Also thanks for the quick reply! Do I need to use any VBA with that or is it strictly a query? Haven't used any strSQL stuff so forgive my ignorance!

  4. #4
    Join Date
    Feb 2011
    Posts
    18
    Quote Originally Posted by healdem View Post
    so you need two queries
    the first finds the number of rows to return, the id field

    the second actually goes and gets those rows using the value of the id field as the top n

    strSQL ="select ID from dealers"

    strSQL1 = select top (" & myfirstrecordset!id & ").....

    why you need to specify the number of rows mystifies me though
    Hi again, it looks like this is something done in VBA, which I have no experience with. Would it be possible for you to write a quick example of what VBA would look like in this scenario, like how I would then tie it to a query? I would really appreciate it!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    do you use ADO of DAO recordsets?
    it doesn't matter, but there's plenty of worked examples whowing how to use recordsets

    run the first query
    then iterate through that recordset in a second query

    you could use the in clause

    eg select my, column, list from mytable where acolumn in(1,2,3,4,6)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •