Jump to content

Summerize unique values


This topic is 6393 days old. Please don't post here. Open a new topic instead.

Recommended Posts


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 ?

Link to comment
Share on other sites

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:


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.

Link to comment
Share on other sites

  • 2 weeks later...
  • 2 months later...

This topic is 6393 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.