Summerize unique values

[Lucky]

Hi

I am trying to create a report that summerizes how many amendments where made on jobs produced in a week.

In the Trailing Grand Summary I have a field that I would like to have the count of Unique Job No's. The problem I have is when a job gets ammended it keeps the same Job No so when I do the count it has many duplicates included.

Is there a way of summarizing on unique Job No's ?

[jmkimmel]

If I understand things correctly, I have addressed a similar situation by doing the following:

• Have a field that is Auto-Number (unique numbers for every record)
• Set up a relationship that is between your table and another instance of itself based on the Job Number field
• Make a calculation that uses this new relationship and create an If or Case function that tests to see if your unique value field is equal to itself in the other instance of the table. If it is, you could have it result in a value of 1 and if not, result in a value of 0.
• Make a summary field that sums this calculated field. (it will add all of them, but they will only be 1 when they are unique)

So the Calculated field would look like:

Case(thistable::reference_field=copyofthistable::reference_field;1;0)

So this calculated field uses the fact that when you look through the relationship to the other instance of the table, you only see one record no matter how many actually match it. So, when the record I am in matches the reference_number field that is the first one on the other side, the value of my calculation is 1. All the rest of the time the value is 0. So, if I add all of these records up, I will really just get what amounts to a count of the unique job numbers.

I hope this helps. If I lost you anywhere, just point out where and I will try to clarify.

[Lucky]

Hi

Thanks for that. I shall try it and let you know how I get on.

[jmkimmel]

Oddly enough, I posted that the other day and then went to a client's site to start on a new project and used a variation on it as I was building some things for them. :)

[Lucky]

I could not get it to work.

Could you create a example to show it working.

Sorry for being a pain.

[jmkimmel]

Sorry to leave you hanging on this. Did you find a way of handling this? If not, I will try to whip up something.