Results 1 to 7 of 7

Thread: top x records

  1. #1
    Join Date
    Jul 2004
    Posts
    18

    Question Unanswered: top x records

    Hi

    I am currently being presnted with a problem that I cannot rectify and hoped you could help.

    I have built a query that gives me data by department by description. However, when I put this data in to a report it is giving me nearly 900 pages. I am trying to build a report that gives me the top 20 records per departmrnt but dont really know how to.

    I know I can build it within the query but then it gives me just the top 20 records altogether not per department.

    Is there anything I can do?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    durrr..

    SELECT TOP 20 *
    FROM yourTable
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Teddy
    durrr..

    SELECT TOP 20 *
    FROM yourTable
    Be nice Ted ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Jul 2004
    Posts
    18
    Teddy Thank you for that reply

    However, it is not the top 20 from the table I need. It is the top 20 from each individual department.

    Eg

    The table has the following data in it

    Dept description retail id cost
    1 credit card 125 20
    1 cash 152 20
    2 debit card 152 20
    2 cash 150 20
    3 credit 125 25
    3 debit 152 25

    What I need the report to do is group the data department and then give me the top 20 retail ids within that department. and the start the next department and then next 20 ids

    Any ideas

    Thanks in advance

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Maybe you could use a subreport instead? I'm pretty sure you can use the TOP operator in a subreport... ?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by sayedbaharun
    Teddy Thank you for that reply

    However, it is not the top 20 from the table I need. It is the top 20 from each individual department.

    Eg

    The table has the following data in it

    Dept description retail id cost
    1 credit card 125 20
    1 cash 152 20
    2 debit card 152 20
    2 cash 150 20
    3 credit 125 25
    3 debit 152 25

    What I need the report to do is group the data department and then give me the top 20 retail ids within that department. and the start the next department and then next 20 ids

    Any ideas

    Thanks in advance
    You could do this with some code ... Have a primary query that returns each department and then have a subsequent query that pulls the requisite 20 records of each department and puts that into the report table ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Jul 2004
    Posts
    18

    Smile

    Teddy I do not think the supreport will work. I've never seen the top x specified within it. If you know of it maybe you could let me know.

    M owen I havent built code for a little while now so am a little rusty can you gove me an example of the code I will be looking for???

    Regards

    Sayed

Posting Permissions

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