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.
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.