Duplicate Records...

[ChiTownDiva]

I know this is probably simple and I just can't see it.

I have a "duplicate" record in the details that I need to suppress.

The thing that makes each record unique is a field called "Updated_Date". How can I write a formula that says suppress the record with the Maximum updated date?

Thanks in advance.

ChiTownDivaus

 

[synapsevampire]

I'm guessing what you mean is that you don't have duplicate data, rather that you want the minimum updated date?

Group by whatever the main data is (customer, etc) the Updated_Date and place your fields in the group header, now you'll only get the first row for that date.

Please post example data and expected output if you need additional help.

-k

 

[ChiTownDiva]

Thanks Synapsevampire....

Is there any other way to do this other than by grouping? Is there a way on the database level to only pull one of the records?

Thanks.

ChiTownDivaus

 

[lbass]

You would still have to group on customer, although you can suppress the header and footer, if you want, using the following method: Go to Report->Edit Selection Formula->Group and enter:

{table.updateddate} = minimum(table.updateddate},{table.customerID})

Substitute your correct field names, of course. You would need to use running totals for summaries using this method, since non-group selected records will still contribute to the more usual summaries.

-LB

 

[synapsevampire]

I think that LB's approach will still require a Grouping.

As for the dataabse, yes, and that's the ideal place to do this.

The query is something like:

select Customer, minimum(mydate)
from sometable
group by Customer

But as you see, you still have a grouping.

Why do you not want to use a grouping? Group by the Customer and use something like:

{table.updateddate} <> maximum(table.updateddate},{table.customerID})

And you'll get all rows except for the maximum, which is what the original post stated. I should have read it more closely, sorry.

-k