one-to-many data retrieve

[dodgyone]

I'm pretty new to CR and would apprecaite it if somebody could help me...

Table 1:

== ========= ========
ID USER      FOLDER
== ========= ========
01 A Smith   A123456
02 J Bloggs  B123456
03 H Jones   C123456

Table 2:

== ======= ========== ========
ID VERSION DATE       USER
== ======= ========== ========
02 01      01/01/2006 Admin
02 02      02/01/2006 System
02 03      03/01/2006 J Bloggs

What I require from the data is to:
1. Establish the [ID] link
2. Find the [USER] if they appear in [Table 2]
3. Using the [VERSION] get the 'DATE' before the record associated with the [USER] if they appear against a record.

So, for [ID] using the criteria '02' I would expect to see:

== ======== =========== =============
ID USER     LATEST DATE PREVIOUS DATE
== ======== =========== =============
02 J Bloggs 03/01/2006  02/01/2006

 

[IanWaterman]

If that is all you require then its quite simple, use a group footer to display your details
Group you report by
User ID and then sort the records by version.

Suppress group header and details
and place the fields you want in group footer. Create a formula using the Previous function

eg Previous(PreviousDateField)

and place that in footer.

Ian

 

[dodgyone]

Thanks Ian.

That has really got me thinking about things in a totally different way to what I had been doing previously. I was trying to chnge things using SQL rather taking everything on board and then formatting the page to suit my needs.

With a little more hands on practice i'm sure I can eventually get to grips with Crystal Reports!

 

[dodgyone]

Is there any way of going back more by than one record (Previous())? The [USER] may be further down the version number list. Thank you:

Table 2:

== ======= ========== ========
ID VERSION DATE       USER
== ======= ========== ========
02 01      01/01/2006 Admin
02 02      02/01/2006 System
02 03      03/01/2006 J Bloggs
02 04      04/01/2006 M Smith
02 05      05/01/2006 Admin

So for this I would need the same result as last time:

== ======== =========== =============
ID USER     LATEST DATE PREVIOUS DATE
== ======== =========== =============
02 J Bloggs 03/01/2006  02/01/2006

 

[IanWaterman]

You will need to detect this in details using a variable

Place formula like this in details
@eval version no.
Whileprintingrecords;

gloabl numbervar version;

If table2 user = table 1 user then version:= versionfield

In group footer add formula
@display Ver No.

Whileprintingrecords;

gloabl numbervar version;

The ; and : are important

Ian