Getting latest result from DB - how?? 1

[Drakhan]

I have a report that requires a formula that I cannot figure out. Here are the details. I have a field that contains any number of "numbers", theoretically, 1 to infinity. What I would like to do is create forumla that will display the HIGHEST number and the information tied to that highest number. Here is an example of the database:

Table1.Number Table1.Data0 Table1.Data1
1 ABC fdslj
2 ABC oiewr
3 ABC mvxcv
1 XYZ gdhgd
2 XYZ ;dsal
4 ABC 450kj
3 XYZ ]perg

What I would like the report to show is:

Highest
Number Data0 Data1
4 ABC 450kj
3 XYZ ]perg

How do I pull just pull the data from the highest number in the Table1.Number field (field is a true numeric value)?? Thanks in advance.

 

[dgillz]

First, group by table1.number, and sort the data so that the highest number is at the top of the group. Then drag the database fields to the group header, and hide the detail section. Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[Naith]

I think he might mean the highest number per Table1.Data0 value, dgillz. If he groups like that, he's only going to get the first Table1.Data0 value per number, instead of the other way around. (e.g. the example would show the two ABC records for group 4 and 3, as opposed to ABC and XYZ respectively.)

If I haven't misunderstood you, group by Table1.Data0, and set your group selection criteria to: Table1.Number = Maximum(Table1.Number,Table1.Data0)

Naith

 

[Drakhan]

Thanks for the quick response! Where do I put this formula? All I ever seem to get is a "Running Total/Summary could not be created" error message. When you say "set your group selection criteria to:" does that mean the select expert? Sorry for these questions, but I am struggling here trying to figure this out. Thanks again!

 

[Naith]

No problem, Drakhan.

To enter that formula in the group selection criteria, go to the Report menu, select Edit Selection Formula, and then select Group.

You should not be creating or modifying a regular formula for this at all.

Naith

 

[Drakhan]

Thanks Naith - very helpful

It worked!