Results 1 to 8 of 8
  1. #1
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168

    Last 10 records for a sorted Category??

    Not quite sure how to phrase the title.



    I wont try to explain why but i have a tbl/frm/qry (actually it is a subform) that uses

    Jan 2021
    Feb 2021
    Mar 2021
    etc

    as names not as dates. so I cant break it down and do a last 6 months criteria in the query.

    now the main form selects which Chapters data to show. and I only want the last 10 records for the selected chapter.

    (i did try looking it up on google, and I thought there was a top 10 type command I could use in the query builder, but I only found SQL solutions on the web and I don't know how to integrate that to what I already Have)

    Click image for larger version. 

Name:	Capture.jpg 
Views:	20 
Size:	162.6 KB 
ID:	46419

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    So what do you think your qry_ChptDuesTrack is?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    to select top 10, just modify your query from

    SELECT tbl....

    to

    SELECT TOP 10 tbl...

    in the query GUI under query properties (display property window the click anywhere in the main query window that is not a table), put what you want in top values property

    However TOP whatever only makes sense with an order - and you aren't doing that so you will just get a random 10. So you need to sort your query by something, maybe a date or an ID and you may need to sort descending if you want the 'last 10' (then pehaps resort in the form)

  4. #4
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    I am not sure I understand the question, but the quick and easy answer is, I think the qry_ChptDuesTrack is a colossal pain in the a**.

    I was hoping to avoid having to explain the long answer but here it goes.

    I have an organization that has different chapters, each chapter pays dues to the State Association.
    In addition to the dues each chapter pays they also need to submit a bank statement and audit form for their account.


    Now here is where things start getting tricky

    The state Association only meets every other month.

    So technically the paperwork and payment are only due every other month

    making things even more difficult, on average

    3 of the chapters will turn in everything on time.
    4 of the chpaters will make a payment and have no paperwork
    3 of the chapters wont have anything at alll
    1 chapter wont even show up to the meeting
    2 of the chapters will have dues waived for "Hardship reasons" and will still owe paperwork but wont have it.
    2 Chapters will be confused and only pay for one of the months they owe.

    Trying to track all of this and issue fines is an absolute nightmare, and so far this is the best I could come up with.
    I dont even remember why, but I tried repeatedly to just use actual date data and could not get it to work the way I needed.
    so I dont like the "dues period" table with Month names but that was the best I could come up with at the time.

    but the data goes back 3 years which (because my back end in in sql server on a "cloud" somewhere in canada, it is slow to load.

    And I really only need to work with the last 6 to 10 months of data for each chapter.

    dont i know this helps or answers your question

    Click image for larger version. 

Name:	Capture.jpg 
Views:	19 
Size:	179.1 KB 
ID:	46420

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    You said
    but I only found SQL solutions
    Your query is just sql ?

    So if you compare one of those solutions, you should be able to see what you need to add.

    @Ajax has now advised you of the first part, and also the fact that you need some sort of order to identify the top 10 ?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    AJAX,

    thank you for the input, but either I am doing it wrong. or that wont work.
    see the image below, when I make it a top 10 (or only return the top ten rows)

    I think it is only returning the top 10 rows in the query.

    and I need it to return the top ten rows for the selected chapter in the query

    Click image for larger version. 

Name:	Capture.jpg 
Views:	19 
Size:	140.8 KB 
ID:	46421

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your should be able to use a calculated field to create an actual date and then sort on that descending and use Top whatever. That is, as long as the day value does not matter. For this, I'd say it doesn't. However because you've put the year first, I can only see this working if you use a function to return the date. That's because I don't think you can use single quotes or chr(34) (double quote character) to concatenate a string to use in the DateValue function. In other words, I don't think things like

    GetDate: DateValue(""" & June 30, 2004 & """") will work. If you're willing to try calling a function from your query I/we can write one. You need to put this function in a standard module, so if you haven't one you'll need to create that too.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the reason for the #name is you are not assigning the query to your form recordsource

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Sorted Records in a Table
    By Joakim N in forum Forms
    Replies: 2
    Last Post: 10-14-2016, 11:36 PM
  2. Replies: 0
    Last Post: 05-31-2016, 01:54 PM
  3. Replies: 1
    Last Post: 01-22-2016, 04:36 AM
  4. Sorted Datasheet
    By RonL in forum Access
    Replies: 3
    Last Post: 09-10-2014, 08:52 AM
  5. Replies: 5
    Last Post: 05-10-2014, 09:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums