Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2012

    Unanswered: Query showing messed up records

    Guys/gals, please help.
    I can't seem to work out a Group By/Last issue in a query.

    I have table called CoursePayments that collects student payments for 10 possible language courses. Students attend more than 1 courses simultaneously. Students pay the membership every month, and thus extend their payment expiration date.

    My table has four important fields:
    RecordID, Student, Course, PaymentExpirationDate

    some data for illustration:
    501 John Smith English 21/09/2012
    500 Pam Wilkins German 18/09/2012
    499 John Smith German 16/09/2012
    498 Jeff Barry Italian 15/08/2012
    497 John Smith German 17/08/2012

    PaymentExpirationDate shows when students' payment is up for the specific course, and an automatic email is made-ready to be sent, when this time comes.

    In order to show which are the last payments made per each course I made a query that shows 'Last record' of each course language a student has paid (LastOfRecordID in descending order; and the other records are GroupBy).
    The problem is that the query will only show the Last record for one Course (such as English, in John Smith's case) but not for the rest (such as German). That is... If I add a new payment for John Smith for English, it will show in the query, but if I add a new payment for German, the query will still show some older record and not the Last RecordID for that course, and thus, I can't retrieve the latest Payment Expiration date.
    I thought that by doing LastOfRecordID, the query would show me:

    501 John Smith English 21/09/2012
    499 John Smith German 16/09/2012

    Instead it shows:
    501 John Smith English 21/09/2012
    497 John Smith German 17/08/2012

    I think I am aware the problem must lie within GroupBy. How can I make the query to show me all the last records per each course?

    tried all my best, have been spending weeks on this, please help.

    best regards,

  2. #2
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 2

    I think I would remove the RecordID from the query, group on Student & Course and use MaxOfPaymentExpirationDate ??

    This would list all combinations of studens/courses with their last paymentexpitydate.


  3. #3
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    You're using non-American formatted Dates, and Access is a dyed-in-the-wool, Red, White and Blue app; I suspect that this is the problem!

    Fortunately, Allen Browne, one of the foremost authorities in the galaxy on Access and a citizen of one of the British Commonwealth Nations, has a paper on the very subject!

    Microsoft Access tips: International Dates in Access

    Linq ;0)>
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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