i have a data base that shows sales rep name in two fields (fname, lname). sometimes the sales rep will change over the life of the account. if it does the old sales rep name is moved to two differnt columns (prev lname, prev fname) and there is a field that indicates the date the change took place.
the report is grouped by the fname, lname (so grouped by current sales rep) and i use a formula to display the sales rep name for each record in the detail (if the sales took place before the change date the prev lname and fname are displayed if after the current sales rep is displayed).
now they want a distinct count of both prev sales rep and the current sales reps.
so if the sales rep changed yesterday but all of the sales on the report are from three years ago the count should show as two. one for the sales rep that actually made the sales and another for the one currently in charge of the account. If multiple accounts are returned for one sales rep the count should be distinct so still just two.
any help is appreciated.
IP Logged
