Calculated field

[Nancy_H._(Guest)]

I^m working with a list of insurance policies in a DBIII database. Each policy record has: a 7-character client code field that starts with the first letter of the last name; and a 1-character (alpha or num) field indicating the branch office it belongs to. I must sort & count policies according an alphabetical split within that branch. I want to assign a two-character Agent code (there is one in each policy record but it^s not accurate, so I can^t use it) to each policy and then sort & count by that agent code. ____For example, the agent with the initials "HC" looks after all clients whose codes begin with A, B, or C in branches 1, A, B, C, and E. Some agents have convoluted alpha splits, e.g. "T2" looks after letters E through I and N through P for branches 9 and F. I need to get the report to look at the client code and the branch code and decide who the correct agent is.____I started to make up an Access database with the agent code and the "low" and "high" possible values for their alpha split but got in over my head because of the multiple branches. I suspect that the answer is really simple and I can^t see the forest for the trees. Can anyone help? ____Many thanks in advance.

[kfleming]

It sounds like you need to create a table with three fields, the first letter, the code and then the agent and then add a relation to this lookup table.____Kathleen__R&R Support__