Filter duplicate records

Hi

Have been given the task of trying to write reports in Crystal after someone found out I new one of Access from the other.

The report is taking data from SAGE database with custom written tables. The problem I have is that one of the custom table has not been well written allowing multiple identical entries.

The table in question holds data for each delivery note posted on SAGE. In this table are field I need relating to Product Number, Quantity and Nett Weight. Due to the way data has been stored I can have up to 16 records (effectively duplicates - same part number, quantity, nett weight) when I only want one record displayed.

Is it possible to write a filter that basically says that if for a given delivery number there is more than 1 record with the same Product Number, Quantity then filter the number of records down to one? If this is not possible, would it be possible to do the same but specifying to pick the record with the highest nett weight? assuming that if there were two identical records for all of the aforementioned fields that it could still bring this down to one?You can do it in your Query:

SELECT ProductNumber, Quantity, 'NettWeight' = MAX(NettWeight)
FROM TableName
GROUP BY ProductNumber, Quantity

(I use SQL Server 7, so you may need to adjust the syntax a bit to work in your database.)

- or -

You can do it in Crystal Reports. There should be a property called SuppressDuplicates (or something similar, I don't have CR in front of me). If you set it to True, Crystal should display only unique records. I think that property is available for fields as well as sections, but I'm not sure. Also, I use CR 8.5, so if you're using a different version, SuppressDuplicates may not exist at all or it may be called something different.