Crystal Reports - comparing multiple records

[Former Member]

Dear Experts,

I am needing a formula which will allow me to compare multiple records. For example, I will have muliple records for an ID:

1114169 74Y CPT SPIKER PHILLIP AT 4/30/2009 11

1114169 74Y CPT SPIKER PHILLIP PA 1/22/2009 14

I need to be able to compare records and determine if either one of records is less than 12. In this example, the first one would be , so I would want to not print either one on the report.

Any help on this would be very much appreciated

[Former Member · Accepted solution]

Kevin,

The only way that would know how to do this would be with a SQL Command.

Something along these lines...

SELECT *
FROM TableName
WHERE ID NOT IN (
	SELECT ID
	FROM TableName
	WHERE RecordNumberField < 12)

HTH,

Jason

[Former Member · Accepted solution]

Or, group by ID, and suppress the format(s) on

minimum({db.field}, {db.ID}) < 12

HTH,

Carl

[Former Member · Accepted solution]

I appreciate your quick response, however, I need to be able to evaluate one records against another. This example will still show the record that was greater than 12 even though the emp had one that was less than 12. I think

[Former Member · Accepted solution]

I appreciate your quick response, however, I need to be able to evaluate one records against another. This example will still show the record that was greater than 12 even though the emp had one that was less than 12. I need to eliminate both records if one for emp was less than 12. thanks

[Former Member · Accepted solution]

In the SQL I provided, The sub-query will generate a list of ALL IDs that have a RecordNumberField < 12. That list is then used to eliminate any ID that is in that list, even if that ID has a corresponding record 12 or greater.

Jason

[Former Member · Accepted solution]

That's not correct. By using the minimum of the field over the entire group in the suppression formula for all of the related formats, all records for the group will be suppressed (or not).

Jason's solution should work as well. I just thought I'd provide a non-SQL possibility...

HTH,

Carl