Expression Help

[johno]

I am stuck on how to write an expression that does the following.

I have the following two tables:

Accounts
Cases

Accounts is a list of our customers. Cases are customer service interaction.


I need to assign a "REP" to each account.

I have added a column called "REP" to the Accounts table. My challenge has
been figuring out how to assign "REP" to the Accounts table by analying the
data in the Cases table.

Here is the business logic I am trying to write in a formula.

1.) Look at all the cases related to each CUSTOMER ID.
3.) IF the account has an OPEN case add that REP.
2.) Otherwise, evaluate the date ranges and choose that REP that last worked
a case for the account.
3.) Add that instance of REP to the record with the same CUSTOMER ID in the
Accounts table.

Here is some sample data.

CASE TABLE

Customer ID Customer Name Rep Status Closed Date
1199646 CreativeArts.com Mitch Close 11/7
1199647 CreativeArts.com Joe Open
1198793 Bridge Packaging Tom Closed 1/3
1192323 Mobile Self-Storage Alonzo Closed 1/5
1192323 Mobile Self-Storage Esperanza Closed 12/8


A currently written formula would come up with the following result.

Customer ID Customer Name Rep
1199647 CreativeArts.com Joe
1198793 Bridge Packaging Tom
1192323 Mobile Self-Storage Esperanza

Thank you for all your help.

John W. O'Grady

 

[tina]

A currently written formula would come up with the following result.

well, looking at the result you posted, it seems that your current formula
is coming up with the correct result, based on the rules you provided. what
is the different result that you're trying to achieve?

hth

 

[johno]

I provided the results manually. I need help writing the formula.

A currently written formula would come up with the following result.

well, looking at the result you posted, it seems that your current formula
is coming up with the correct result, based on the rules you provided. what
is the different result that you're trying to achieve?

hth

I am stuck on how to write an expression that does the following.

[quoted text clipped - 41 lines]

John W. O'Grady

 

[John Spencer]

Is this a one-time effort? If so, why not use two update queries. First
query would search for an OPEN case (what happens if you have two Open cases
for one account?) and assigns the rep that way. Second query would need to
take care of the remaining.

Update Query One -
Assumptions: Only one OPEN case for any customer
UPDATE Accounts INNER JOIN Cases
ON Accounts.CustomerID = Cases.CustomerID
SET Accounts.RepId = Cases.RepID
WHERE Cases.Status = "Open"

UpdateQuery Two:
UPDATE Accounts INNER JOIN Cases
ON Accounts.CustomerID = Cases.CustomerID
SET Accounts.RepId =
DLookup("RepID","Cases","CustomerID=" & Accounts.CustomerID & " AND [Closed
Date] =#" &
DMax("[Closed Date]","Cases","CustomerID=" & Accounts.CustomerID) & "#")
WHERE Accounts.RepID Is Null

Of course, you might be able to do this all in one with the following
Assumption: Status is OPEN if Closed Date is Null

UPDATE Accounts INNER JOIN Cases
ON Accounts.CustomerID = Cases.CustomerID
SET Accounts.RepId = Cases.RepID
WHERE NZ([Closed Date],#1/1/3000#) =
(SELECT Max(Nz(T.[Closed Date],#1/1/3000#))
FROM Cases as T
WHERE T.CustomerID = Cases.CustomerID)

TEST this on a copy of your data.