Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2007
    Posts
    1

    Getting the most current record for an invoice

    Hi, i'm not 100% sure whether the problem i'm having is structure related or whether it's because my SQL/queries isn't the best, basically i have a table which contains a PK which is an autonumber so records can be uniquely identified, a batchnumber field, a invoicenumber field, a batchdate field, and the rest of the fields contain details about the invoice ie invoice amount, etc
    Below is an example of the relevant fields in the table:

    PK batchnumber invoicenumber batchdate
    0 2112 10123 10/04/2007
    1 2112 10152 10/04/2007
    2 2521 10123 14/04/2007
    3 2130 10155 12/04/2007
    4 2525 10123 11/04/2007

    Basically what i need to be able to do is build a list of the most current record of all invoices as of a specified date, for example if the date specified was 11/04/2007 then the output would be

    PK batchnumber invoicenumber batchdate
    1 2112 10152 10/04/2007
    3 2130 10155 12/04/2007
    4 2525 10123 11/04/2007



    Any suggestions would be greatly appreciated. Thanks

  2. #2
    Join Date
    Feb 2008
    Posts
    2

    Similiar problem

    Hello, I have a similiar question. I noticed there hasn't been a reply since your post a few months ago, i was just wondering if you figured this out somehow. if so, would you please clue me in on how you solved the problem.

    thanks.

  3. #3
    jya is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2007
    Location
    Chicagoland
    Posts
    109

    Shot in the dark...

    I am not sure I follow this question, but I am thinking you may want to run two queries. The first query would be a Total query where you group by invoice number and look for the Max(PK). In other words, you would find the most recent record for each invoice. The second query would consist of the original list and the Total query linked by the PK where you can display the other information associated with the most recent record.

    Hope this helps.

  4. #4
    Join Date
    Feb 2008
    Posts
    2

    Thank you

    your response was very helpful.

    thank you!!!!

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

Similar Threads

  1. Email a report of the current record.
    By jonudden in forum Reports
    Replies: 1
    Last Post: 10-11-2007, 07:42 AM
  2. Duplicating lines in an invoice report
    By GordonEdinburgh in forum Forms
    Replies: 0
    Last Post: 04-21-2007, 12:20 PM
  3. Replies: 0
    Last Post: 12-11-2006, 04:55 PM
  4. Mailmerge Current Record...
    By oakleyp2 in forum Programming
    Replies: 0
    Last Post: 02-22-2006, 08:08 AM
  5. How to output only current record in query
    By mslieder in forum Access
    Replies: 0
    Last Post: 01-20-2006, 05:48 PM

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