Populating a field with a max value from another field for groups of records

[gfr92y]

 

I feel like I should know how to do this, but I keep going around in circles. (I have tried to demonstrate what I am trying to do below.)

Simply put, I want to populate the field Last Call Date with the date of the last sales call, i.e., Call Date, for each company. (I think I am OK on the sorting afterwards.)

Two more things...

1. I need the values in the Last Call Date field to remain unchanged when the data is not sorted, and
2. I can update the Last Call Date when needed. However, it would be nice if it updated itself anytime a more recent Call Date was entered. 

I would appreciate any assistance!

Thanks.

Joe

Edited August 20, 2015 by gfr92y
Add one last thing

[comment]

Do you have a table of Companies? And a table of SalesReps? And a table of Calls? Or is it all just one giant flat table?

[gfr92y]

CUSTOMERS - A table of customers where each CustomerIdNumber is an auto serial number

SALESREPS - A table of sales reps such that:

1. Each SalesRep-to-Customer relationship is one-to-one
2. Each ContactIdNumber is the concatenation of the CustomerIdNumber, a hyphen, and the auto serial number generated when the sales rep was entered into the SALESREPS table

CALLREPORTS - A table of call reports such that:

1. Each CallReport-to-SalesRep relationship is one-to-one
2. Each CallReportIdNumber is the concatenation of the ContactIdNumber, a hyphen, and the auto serial number generated when the call report was entered into the CALLREPORTS table

Please let me know if any other additional information might be helpful.

Thanks for your assistance.

Joe

 

[comment]

I believe you mean one-to-many (one customer has many sales reps, one sales rep has many calls), not one-to-one.

In any case, assuming calls are entered in chronological order and that the relationship does not sort records otherwise, you could define a calculation field in the Customers table (result is Date) =

Last ( CallReport::CallDate )

Alternatively:

Max ( CallReport::CallDate )

will do the same thing, at a very slightly increased performance cost (but then records can be entered/sorted in any order).

[gfr92y]

I have 116 customers each with its own sales rep and 11 sales reps each with 10-12 customers and are responsible to "call" (i.e., visit, call, or email) each customer 4-5 times per month.

How does Max ( CallReport::CallDate ) know to calculate the maximum value of each Customers' CallDates individually and then populate the LAST CALL DATE field with the maximum value by customer?

Sorry if I did not make this intent clear originally.

 

[comment]

I am afraid you have managed to confuse me now. Are the following statements true?

1. A customer can have any number of sales reps assigned to it;
2. A sales rep is assigned to only one customer;
3. A sales rep can make any number of calls;
4. A call is made by one sales rep only.

If you have answered yes to all four questions, then you have two one-to-many relationships, and your relationships graph should look like this:

Customers -< SalesReps -< Calls

In this arrangement, a call made by a sales rep is also related to the grandparent company. Therefore Max ( Calls::CallDate ), when evaluated from the context of Customers, returns the date of the most recent call made by any one of the sales reps assigned to this customer.

 

Unrelated to your question, but could be important: in this setup, if you reassign a sales rep to another customer, all their calls will be reassigned along. Also, no history of rep assignments is being kept.

 

 

Edited August 20, 2015 by comment

[gfr92y]

 

I apologize for introducing unnecessary confusion in my last post. (Once I am finished answering your questions, I will go back and revise/delete heavily.)

A customer can have any number of sales reps assigned to it;

No, a customer can only have one sales rep assigned to it.

A sales rep is assigned to only one customer;

No, a sales rep is assigned to either 10, 11, or 12 customers.

A sales rep can make any number of calls;

Yes, a sales rep may call a customer as many times as they like.

A call is made by one sales rep only.

Yes. It is rare that two sales reps would participate in a call.

I believe you when you say Max( CallReports:CallDates ) will work. Why it will work escapes me, but isn’t that to be expected; at least initially.

I will create a table: Customers:CustomersName, SalesReps:SalesRepsName, CallReports:CallDate, and LastCallDate , a new calculated field: Max(CallReports:CallDate).

I still do not know how the Max function will distinguish between DowChemical’s Call Dates and ExxonMobil’s Call Dates, but I will not let that prevent me from implementing your solution, if only on faith! …and that is OK!

Thanks

I appreciate your two closing comments. The good news is that I have not really overlooked these issues. They are not within your field of view, which I intentionally limited so we would not trouble ourselves with the rest of the mess I created.

[comment]

No, a customer can only have one sales rep assigned to it.

That would make sense - but in your original post you show calls to Dow Chemical being made by Pete, Joe and Steve. I don't see how that goes together with the above statement.