I'm relatively new to Crystal Reports(Using version 11) and have a goal that I have yet to discover how to reach. Details as follows:

I'm attempting to make a scoring report. The data for the report is largely contained in one table, which has a record for each time a test taker has taken a test. In that table there is information like name, date taken as well as the score for that particular test taken. In this testing system, test takers can take a test more than once(assume twice for the purposes of this example) which will be clearly marked as pre for the first and post for the second time taken.

Now, what I have to do in this report is take the pre and post score for two test takers and place both(each from a separate record in the same table) on the same line in the report, then display a delta(the change in the score from pre to post). Doing this is where I have come to meet a brick wall.

Assumption: There is no means to modify the tables to place the pre and post scores in separate tables or to add the post score to the same record as the pre score(if only it were so simple).

I've attempted to use a SQL expression field to pluck the post score out of the table to place it there and use it for the delta, but I'm not clear on the syntax for full on SELECT SQL statements in those fields and I get syntax errors. Is there some other means to do this? Or is there a good online guide to SQL syntax in SQL expression fields?

Any assistance I could get would be appreciated. Thanks.

Edit: New title because the last one made little sense.


Edited by Darkren - 02 Oct 2007 at 7:14am