Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Posts
    154

    Question Unanswered: Displaying only one record in a subreport

    I have an access 2000 db that I have an Order table with OrderJobNo as the key field and I have a one to many relationship to a ProductionNotes table( through jobno as a foreign key) so that people can right many dated notes about the same order. I am wondering if there is a way to attach this notes table info as a subreport such that ONLY the latest dated note shows up under each job number. Right now I have this kinda half-baked arrangement where I have sorted the query for the subreport by date descending and then made the subreport so that it is only big enough to display one record and changed CanGrow on the subreports to NO. Thank you for your time.

  2. #2
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188
    it may be quite simple by creating a query (for the subform) that sorts date field descending and has MAX RECORDS property set to 1. this would also increase your app performance.

    hope this helps
    qha_vn

  3. #3
    Join Date
    Nov 2002
    Posts
    154

    Question My DB

    The problem with setting my maxrecords to one is this--the notes table is set up like this: key field ProductionNotesID-autonumber, OrderJobNo--text, ProductionNotesName--number, ProductionNotesDate--Date/Time, ProductionNotesNote--Memo. I had to use the autonumber as the key field to enable multiple notes on the same order on the same day from potentially different people. So, unfortunately though I can easily get a beautiful list of notes sorted by OrderJobNo then ProductionNotesDate I can't seem to select just the OrderJobNo field to supply only one record(in this case the latest dated) for each OrderJobNo. OrderJobNo is the key field for the master Orders table. thank you for your time.

Posting Permissions

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