Sorting by Date between different groupings

[wagspk]

I am trying to generate a report from a Subform which contains the following
fields:

Book Number
Date Issued
Issued To
Date Returned

In the Form each Book Number will have a seperate entry for each time it was
lent out to somebody. Eg.:

Book Number; Issued Date; Issued To; Returned Date

1; 1/1/2008; Bill; 2/2/2008
1; 3/3/2008; Bob; 4/4/2008
1; 5/5/2008; Kevin; 6/6/2008
1; 7/7/2008; Mary ; 8/8/2008
2; 1/5/2008; Kevin; 1/19/2008
2; 5/12/2008; Graham; 5/29/2008
2; 6/1/2008; Mary; 6/16/2008
2; 9/1/2008; Graham; 10/15/2008
3; 3/4/2009; Bill; 3/7/2008

and so on...

Now I want to generate a report which has one line for each Book and then
shows the information for the 3 most recent entries for each Book on the same
line going from most recent on the left, to the oldest date on the right. And
then I want the entire report sorted by the most recent return date for each
Book, but in reverse order. The above example should then look like this:

Book Number; Issued Date; Issued To; Returned Date; Issued Date; Issued To;
Returned Date; Issued Date; Issued To; Returned Date

3; 3/4/2008; Bill; 3/7/2008; ; ; ; ; ; ; ; ;
1; 7/7/2008; Mary; 8/8/2008; 5/5/2008; Kevin; 6/6/2008; 3/3/2008; Bob;
4/4/2008
2; 9/1/2008; Graham; 10/15/2008; 6/1/2008; Mary; 6/16/2008; 5/12/2008;
Graham; 5/29/2008;

I am having all sorts of problems trying to sort this out. Does anybody have
any ideas?

Peter

 

[Duane Hookom]

I would use a main report based on the unique Book records and a 3 column
subreport based on the transactions. Don't allow the subreport to expand so
it will only display the 3 records max for the transactions.

To sort the entire report, you need get the Max(ReturnDate) into the Record
Source of the main report. This can be done by creating a totals query:
SELECT BookNumber, Max(ReturnedDate) As MaxRet
FROM tblBookTrans
GROUP BY BookNumber;

Add this totals query to the main report's record source. If your main
report only needs the book number, then this query can be the main report's
record source by itself.