Master-Detail - want most recent detail

rbuser

In report builder, I have a master query A and the detail query B. B is of
course the many part of the master-detail relationship. I have two data
queries in my report. What I want to do is for each record in A show the
record from B with the oldest (or newest) DateOfEvent field.

How would one do this in ReportBuilder (without editing SQL statement
hopefully).

Thanks in advance!!

rbuser

I noticed even if I edit data query B (with a custom SQL statement), I can
no longer JOIN B with A. So, how would one get the most recent B record for
each A record?

digitalmetaphors

An easy way is to add a calc field on the detail dataset. If you don't want
to add that to every record in the detail, then do a join and calc it on the
master.

Do a join on master and detail, for example, I used Customer - Orders
DBDEMOS tables. I did an inner join on CustNo. Then selected only the
customer fields. Then added a calc field on Max(Orders.SaleDate) and added
that as the only selected field on the joined dataview other than the custom
fields. This results in a max calculated field on the master dataset with
only the 54 customer records. Then you can still link the detail orders
dataview to this master and print the report as you were. I'm emailing you
an example I put together.

As far as other approaches, you cannot create an expression type search
criteria value on a calculated field.


Cheers,

Jim Bennett
Digital Metaphors