Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Help with Query

  1. #1
    Join Date
    Feb 2004
    Posts
    214

    Exclamation Unanswered: Help with Query

    I want a query to display a certain number of records. Example: I have a query that usually displays 100-200 records. I want a query so that the user specifies a number like 50, and the query will display the first 50 records in that query of 100-200. Also, I can not use VB code, it will have to be in SQL or access query parameters.

    I appreciate any help that I can get on this.


    Thank You!

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    I'm not sure if you can use TOP keyword with a query parameter. I believe to do this, you need to create your sql on the fly. which involves either macro or VB.

    Your other option could be instead of letting user to choose result number, you could create different queries with template numbers like 10, 25, 50, 100 and let user choose query from a list. thats all I can think.
    ghozy.

  3. #3
    Join Date
    Feb 2004
    Posts
    214

    ...

    That actually does help and I think I may go that route.

    Thanks!

  4. #4
    Join Date
    Feb 2004
    Posts
    214

    ...

    actually what I had in mind wont work.

    What about this:
    I have a user that enters in new info daily. I need that user to be able to run an update query that assigns 20% of those records to 5 different groups so that in the end, each days records (no matter how many) will be assigned evenly.

    Any ideas?

  5. #5
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    I'm going to go home at the moment just a quick response TOP keyword works with percentage in access. you could try that I guess. if it doesn't help I will try to help when I'm home.
    ghozy.

  6. #6
    Join Date
    Feb 2004
    Posts
    214

    ...

    That was my original idea. But I don't think it would work though.

    Example:
    Query has 100 records
    1st run-displays 20 records
    2nd run-displays 16 records
    3rd run-displays 13 records
    4th run-displays 11 records
    5th run-displays 8 records
    etc....
    My query is set up so that it displays all records with a certain field that are null. Once the query is ran and it updates the top 20% of the records, those will fall off of the query.
    If there is another way around this, let me know

  7. #7
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    you know doing this without VB is really sweating . I assume you don't delete any records, instead you sign them proccessed and keep outside of the query next time.
    I think I found a way but I need to cook dinner. if you can do it please leave a note here. if you don't I will answer in few hours.
    ghozy.

  8. #8
    Join Date
    Feb 2004
    Posts
    214

    ...

    Not using VB being a pain, tell me about it = )
    That was one stipulation that my work gave me.

    You are correct, none of the records are deleted, they are just marked complete so they fall off of the query.

  9. #9
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    alright you need 2 queries:
    1st Query pulls up %20 of unmarked records, you can also use this one to show records going to be updated.
    and 2nd Query is an update query, which uses 1st query as source and updates records as marked.
    that should do the job.
    example SQL
    1st query get list of %20 of uncompleted records, lets assume this is saved as Query1:

    Query1:
    Code:
    SELECT TOP 20 PERCENT ExampleID, ExampleField, Complete
    FROM Table1 WHERE (((Complete)<>True));
    2nd Query uses Query1 as source and updates [Complete] field to true:
    Code:
    UPDATE Query1 SET Query1.[Complete] = True;
    ghozy.

  10. #10
    Join Date
    Feb 2004
    Posts
    214

    Will that work?

    Say I have 5 different people to assign accounts to and there are 100 records(which this number will flucuate daily)
    My query shows all accounts that are unmarked with a persons name.
    Once I run that second query, my 100 accounts goes down by 20 since it was marked with a persons name so it will show 80 now. The next time I run it, it will take 20% of the 80.

    I will need to assign 20% accounts to each of the 5 people which would be 20.
    So the first 20 accounts go to Bob
    The next 20 to Suzy
    etc...

    Will your queries work for this?

  11. #11
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    nope I didnt know you mark your records with person names. my query works for a boolean field as you see.
    ghozy.

  12. #12
    Join Date
    Feb 2004
    Posts
    214

    ghozy

    Well, I figured it couldn't be done without VB.
    I do thank you for you hard work on this though!

  13. #13
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    no problem, it was a good excercise.
    ghozy.

  14. #14
    Join Date
    Feb 2004
    Posts
    214

    Ghozy

    Is there an easy way to do this with VB?
    I'm talking like less than 10 lines of code?

    What would I would prefer is this,
    The user enters an associate name and the number of records.
    Then an update query runs and assigns the top "whatever the user entered" records to that person.

    How hard would this be to do?


  15. #15
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    it wouldn't be that hard to do it in VB. if you want me to write a snippet, you need to write some more info about your table or better attach your database.
    Last edited by ghozy; 08-11-04 at 14:36.
    ghozy.

Posting Permissions

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