Help with arranging table data

[cdj01]

I am running CR 8 off of an oracle database. The data I need is in the table result_archive. It is arranged like this:
labnumber test sequence value mnemonic
(many more fields but these are the ones of interest)
This is a database of laboratory test results. Each labumber may have several tests of the same type done, each new one getting a higher sequence number. Each different lab test has a different code.
What I am trying to do is compare the results of two different test codes. What I need to do is set it up so that the highest sequence of test 10001 is compared to the highest sequence of test 07001 (they will have different 'highest' sequence numbers). And I need to show this where test 10001 has a mnemonic of 'abnormal' and test 07001 has a mnemonic of 'ff'.
What I am envisioning is this in the details section:
20031111234 10001 99 4.5 abnormal
20031111234 07001 04 1.1 ff

Is this even possible in crystal? So far the best I have come up with is to run a report for each test individually (test 10001, menmonic 'abnormal', sequence 99) and export it to excel, doing the same for the other test, and then pasting one into the other's excel sheet.

 

[lbass]

If you mean you want the highest sequence of tests with mnemonics that equal ff and abnormal, then use the following for your record select statement:

{table.mnemonics} in ["abnormal","ff"]

Then group by {table.test} and go to report->edit selection formula->GROUP and enter:

{table.sequence} = maximum({table.sequence},{table.test})

Or, did you want to see the highest sequence and then check to see if the mnemonics are "abnormal" or "ff"?

-LB

 

[cdj01]

What I am trying to do is find out which labnumbers have test results where test 10001 is 'abnormal' AND test 07001 is 'ff'. I don't want the other results. It's this combination of different test results that I need. This database contains about 300,000 different labnumbers for each year, with each labnumber having 11 different tests, each test done multiple times. So If I just pull the ff's without the other test being abnormal, I will get tens of thousands of unneccessary results.

 

[lbass]

You didn't really answer my question, but you could try the following:

({result_archive.test} = "10001" and
{result_archive.mnemonic} = "abnormal&quot or
({result_archive.test} = "07001" and
{result_archive.mnemonic} = "ff&quot

That should return all tests meeting your initial criteria.

Then create a formula {@both} for the details section:

if {results_archive.test} = "10001" then 1 else
if {results_archive.test} = "07001" then 10000

You should group on labnumber and then on test, and then enter the GROUP select formula:

{table.sequence} = maximum({table.sequence},{table.test}) and
sum({@both}, {result_archive.test}) > 10000 and
remainder(sum({@both}, {result_archive.test}),10000) <> 0

Now you would have the highest sequence number for those tests which are 10001 and &quot;abnormal&quot; and 07001 and &quot;ff&quot; for those labnumbers which have both tests. But note that these might not be the highest sequence numbers for those tests in the archive if there were tests with different mnemonics, i.e., what if there were a &quot;normal&quot; test with a higher sequence number?

-LB

 

[cdj01]

If there was a normal test with a higher sequence number, then it would be invalid for my criteria.
I managed to get the data I needed through Toad, our oracle application, by running an sql query there.