Home End User
New Blog Posts: Merging Reports - Part 1 and Part 2

Master-Detail - want most recent detail

edited December 2002 in End User
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!!

Comments

  • edited December 2002
    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?

  • edited December 2002
    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

This discussion has been closed.