Lucky Posted September 11, 2006 Share Posted September 11, 2006 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 ? Link to comment Share on other sites More sharing options...
jmkimmel Posted September 13, 2006 Share Posted September 13, 2006 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. Link to comment Share on other sites More sharing options...
Lucky Posted September 14, 2006 Author Share Posted September 14, 2006 Hi Thanks for that. I shall try it and let you know how I get on. Link to comment Share on other sites More sharing options...
jmkimmel Posted September 15, 2006 Share Posted September 15, 2006 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. :) Link to comment Share on other sites More sharing options...
Lucky Posted September 25, 2006 Author Share Posted September 25, 2006 I could not get it to work. Could you create a example to show it working. Sorry for being a pain. Link to comment Share on other sites More sharing options...
jmkimmel Posted November 27, 2006 Share Posted November 27, 2006 Sorry to leave you hanging on this. Did you find a way of handling this? If not, I will try to whip up something. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6487 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 accountSign in
Already have an account? Sign in here.
Sign In Now