Crystal Reports and history tables

[phellis]

I have a couple of tables that store the status history and the address status of a person. In SQL the query would be something like

Select P.Forename, P.Surname,
S.Status,
A.Line1, A.Line2, A.Line3, A.City
From Person P
Join StatusHistory S
On P.PersonUID = S.ParentUID
And S.Date = (Select Max(Date)
from StatusHistory S1
where S.ParentUID = S1.ParentUID)
Join AddressHistory A
On P.PersonUID = A.ParentUID
And A.Date = (Select Max(Date)
from AddressHistory A1
where A.ParentUID = A1.ParentUID)

Can the equivalent of this be done directly in Crystal Reports 10 without using the Command connection type or any SQL?

 

[Madawc]

It would help if you'd say what you're wanting to do, rather than how you'd do it in SQL.

Crystal doesn't allow you to select records on the basis of some property of a group of records. But you can get the same effect by other methods.

If you want the latest personal details, group the personal records (or personal+status linked using Database Expert). Use a summary total to find the maximum date: thats got by right-clicking on the field and choosing insert.

You can suppress details for a group and just show the group header or footer, which can be the latest date, depending on Report > Record Sort Expert.

Does this help?

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[phellis]

This does help thanks.

As to what I'm trying to achieve is get the latest status and latest address for the members.

The Person table contains
PersonUID Forname Surname
1 Joe Blogs
2 Jim Jones

The Address table contains
ParentUID Date Line1 Line2 Line3 City
1 1/1/97 1 A Steet A Village A City
1 25/4/03 34 new Street Somewhere
2 23/10/04 19 crystal st

The Status table contains
ParentUID Date Status
1 25/3/04 In Work
1 26/5/04 holiday
1 30/5/04 In Work
2 23/11/04 In Work
2 7/10/05 Left

So from the Address and the Status tables I need to get the latest Address Values and Status values.

So in the case of Joe Bloggs the report would return
Joe Bloggs Current Status: In Work
1 New Street
SomeWhere


I have found that by using the grouping of the Forname and Surname that I can then sort the address and status tables by the dates descending and then putting the details on the group header seems to give me the required information.

However crystal will be bring back 5 rows from the database to do this when they are linked through the Database expert.

Normally I just do this using the SQL Command connection but I'm trying to show somebody with no SQL knowledge how to create the report hence trying to create it completely within Crystal.

 

[Madawc]

Crystal often brings back more data than you want. Use group headers or footers, suppress the details.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10