Only one record please! 1

[rier]

I have a report that is grouped by a CustomerID.
The details show two records relating to that customer, but I only want to show the record with the minimum(date)!

I know I should know the answer, but can anyone help please?

Thanks

Richard

 

[SteveFairclough]

I'd be inclined to sort the records by Customer ID, then Date (whatever the date field is that your refer to). The date should be sorted in ascending order.

You can then supress multiple records in the detail line by using the Previous() function.

eg. In the Suppress section of the detail section, put in
Previous(CustomerID) = CustomerID

This have the effect of only displaying the first record of each unique CustomerID and as it's sorted by Date as well, each one shown will have the minimum date.

I hope I've explained that OK and I hope it helps.

 

[rier]

Stanfa

Perfect.
Incidently, I also found a workaround. Initially I had a Group1 based upon the CustomerID and put various fields in the details section. This is why I ended up with more than one record per customer. By moving the fields from the details section to the Group1 section and replacing the [date] field with a formula that read 'minimum({dbo.date})'. This then only detailed the earliest record relevant to each customer - make sense?

Anyway, your help was much appreciated and you taught me something new!

Richard