My query keeps updating!!!

[jrabenhorst]

I've created a query for applications that come through our insurance company. Each application requires inputting a policy #, an agent name, and an agent number. Because each agent has a specific number, I created a seperate table with agent names and numbers so that my form will automatically fill in the agent name when the number is inputted. However, agents come and go quite frequenty, and when I change an agent name in the agent table, it automatically updates all previous records of that number with that new names and deletes the original name. Any ideas on how to prevent this from happening?

 

[AlexMidd]

Each new agent should have a new number if you are using that number as an ID (Primary Key) along with referential integrity within the database, i.e,. do not reuse numbers when agents change.

Have fun! )

Alex Middleton

 

[jrabenhorst]

We've always reused agent numbers. Is there anyway to prevent it from updating those names?

 

[AlexMidd]

Not if you are using the numbers to reference the agent's name in the policy table.

A question: If the agent with that number ceases to exist, what happens to the agent details in the record for that policy, e.g if policy 1 has agent A, and agent A goes out of business for example, I presume you still need agent A's name to appear on that policy? If so, you need to create and use a different ID for your primary key, i.e. not the agent number that you are using and reusing when agents change. Another way is to store the actual agent's name in the policies table, but that kind of breaches normalisation rules.

Have fun! )

Alex Middleton

 

[jrabenhorst]

You might have given me an idea to make this work... I let you know, thanks!

 

[jrabenhorst]

I lied. I tried to change the primary key, but I still can't use the old agent number and prevent it from updating the previous records. And yes AlexMidd you are correct, the agent name needs to travel with the policy even if he leaves the company. Any other ideas?

 

[AlexMidd]

I don't understand. If you add a new field and use that as the primary key, you then ned to use that number (in the background) to tell the policy table which agent applies to each policy (i.e. add that field to the policies table and use it as a foreign key) instead of the agent number.

So your agent table would now look like, for example, where the ID field is the new Primary Key field:

[b]AgentID | AgentNo      | AgentName[/b]
--------+--------------+-------------
1       | 10001        | Agent A
2       | 10002        | Agent B
3       | 10001        | Agent C

So, Agent A has gone and Agent C has taken over the agent number, but you use the [ID] field as a foreign key in the policy table to denote the agent:

[b]PolicyNo | Insured      | AgentID[/b]
---------+--------------+---------------
ABC12001 | Mr Smith     | 1
ABD13002 | Miss Jones   | 2
ABX21003 | Mrs Black    | 3

This would pull out agent number 10001 for Mr Smith and Mrs Black's policies, but show the correct agents' name for each of them.

Hope this makes sense.


Have fun! )

Alex Middleton

 

[jrabenhorst]

Yea that makes sense... But that means that our staff would need to know each agents specific IDNumber, as opposed to their agent number. They are familiar with the agents based upon their agent number. No IDNumber exists. I was hoping their was an easier way. O well thanks for the help anyway.

 

[AlexMidd]

No they wouldn't. As I said you would use the AgentID in the background. Your users need not even be aware of this number. You can use a combo box to select the agent. This would show the AgentNo and AgentName, not the AgentID, but it would have the AgentID as the bound column in the background to insert that into the foreign key field in the policy table. If no user selection is required, just use a lookup to show the AgentNo and AgentName, with AgentID used in the lookup but only returning the required fields for display.

Have fun! )

Alex Middleton