Apr 10 2012, 07:55 AM
I am in the process of building my 1st database for an insurance agency. Ten agents and approximately 8,000 clients and growing. When I began the database design , it seemed easy enough as each client, although they had multiple policies, they only had one agent. Now I find out that One Client can have multiple agents, as well. Originally I had the input form 1 to be the Client Info and this is where I associated the client with an agent (combo box from my agent table). Then after saving the client info, the next Form to open would be the policy sale entry, carrying along with it the Client's name and the agents name already. after completing the sale info, on save the next form opens to enter commissions, again carrying along the client and agent--just FYI. Now, I am told that the client can be sold different policies by different agents. My 1st thought was just then associate the agent with the sale of the policy only. Would this be correct?
Why I am worried is because the main info that they wish to extract from this database is to be able to 1) look up the client and the policies when the client calls with a question. And 2.) to be able to sort the clients by agent in case of repeat visits. A normal report (or printable form?) that will be used often will be Agent A needs a list of all his clients in the County B. Since I haven't even gotten far enough to begin reports yet, ( I really am just a beginner) I would like to know if I associate agent only with sale, will I be able to get a print out like that without showing Mary Jones on the list 3 times for her 3 policies (Just once as a client)? It is okay, if Mary Jones showed up on the list for each agent she may have.
My second choice, was to have a multi value field on the client info form and I could select both agents there when the time comes. It is probably only 50 clients out of a thousand clients that have multiple agents. So Mrs. Jones probably already has two policies with Agent A but then she may be sold a third by Agent B. This would require the person inputting the sale, to open the Client Info form and choose the second agent then.
If anyone could advise on what the best way to do this would be, that would be great. I am getting a lot of pressure to get this database to a stage where I can begin entering info and they can use it to look up clients. All the bells and whistles can come later. They were using an unbelievably unwieldy spreadsheet that someone started deleting cells only then sorted so now none of the info matches up correctly across the columns. They have no way to access anything without going to the paper file cabinets.
Thank you for any advice you can offer.
Apr 10 2012, 08:00 AM
It would help if you could upload a zipped copy of the Db so we can take a look at the structure
Apr 10 2012, 09:15 AM
You have a many-to-many relationship. These relationships are implemented through a junction table which sits between the two related tables. Handling this properly will require removing the agentID from the client table and putting it into the new junction table. This is additionally complicated by the fact that policies are also related to the client and agent, not just the client. I do not think the relationship is actually just between client and agent, in fact. The relationship involves client, policy AND agent. After all, a client could have an auto policy with agent "A" and a second auto policy with agent "B". Not likely, I grant, but possible.
Here's a basic structure.
ClientID FK to client table
AgentID FK to agent table
(other attributes of the policy)
I realize that refactoring this database is not going to be a small task, but you do need to make it conform to the actual business rules you have to support.
Apr 10 2012, 09:24 AM
Only because it is mentioned in the title of the thread. The Evils of Lookup Fields in Tables
Apr 10 2012, 09:24 AM
Firstly it is vitally imortant that you get you database stucture correctly designed and properly normalised - this will make you future updates (the bells and whistles) a lot easier (and possible!) These references will point you in the right direction:
Most experienced developers woud advise you to steer clear of Multi Value Fields until at least you are fully aware of the implications of using them, and use a more standard junction table instead (As George has already posted!).
Apr 10 2012, 10:15 AM
I agree with all the comments so far. Avoid MVFs and try to implement the new structure that George offered. That is, associate the agent to the policy instead of the client. You can use queries to connect everything else together for your reports.
Just my 2 cents...
Apr 11 2012, 07:52 AM
Hello again. I have attached a copy of my database. I am a little embarrassed of it because it does have queries and information that were experiments in me trying to figure things out. Here's the thing. I actually started trying to teach myself access back in the summer of 2007 using Access 2003 to help out 2 friends in the insurance business. I got as far as my database is now. Then one of the agents moved and I abandoned the project. Now, the agent that stayed here has formed a bigger agency in the ensuing years and had been keeping his clients in a crazy spreadsheet. Someone deleted some cells, sorted the list and bamm, now the spreadsheet is useless. So I am back up at bat….using Access 2007 and having forgotten most everything I have learned and an urgency to get this done yesterday.
I had asked about associating the sales with the agent and/or client. And a junction table was suggested. I believe I started it that way. And that is what I have with the policy table??
I really really really need help with normalization, making sure that I can sort the database to produce a list of all Agent A’s client by a specific county. And not end up getting Client Mary Jones 3 times because she has three policies with us. It is okay if Mary Jones has two agents and she ends up on both Agents lists—that is what we want to happen. Which was my question about the multi-value field.
For normalization purposes, I am stumped at the level of necessary normalization for the addresses. County wasn’t important before, but now it is the main focus. I plan to make that a look up table because Counties won’t change. Now I have the address in a table with the client info. Put I believe an address table is more appropriate. Yes, 2 clients may share the same address. (usually husband/wife) But I really don’t want to break out house number from the street and putting cities and zip code and in their own tables seems overwhelming to me. But I will be dealing with 8000 clients and growing so I don’t want a problem in the future.
The second question I have with normalizations is commission table. Right now the commissions are only be recorded as an FYI but eventually if I can get this working right, I’d like the agency to be able to count on it as a way to really process payments. Right now all that is done by hand. Each agent is paid an Advance at time of Sale and the A second amount called an Override and the a third amount is the residuals(also called renewals) that last 7 years or until cancel. I had all these things 1 table, then made a residuals table and then put it back in commission table. Which way would be the best way to go?
If you have any other tips or ideas on how to make this thing operational as fast as possible I would really really appreciate it. I figure I can design better forms and reports as the information is being inputted and utilized but the structure has to be right before that can happen.
ANY help would be so appreciated.
Thank you all!
Apr 11 2012, 11:08 AM
I have only taken a quick glance at your Form Structures
You are going about this in the wrong way
A Client has Sales
1 Client can have Many Sales
You are basing your Form on a query that incl;udes BOTH Client & Sales
What you actually need is:-
The Main Form Based on the Client
This Main Form would then be linked to a Subform based on Sales
This structure allows you to see the Parent Record of the CLIENT
with the Subform automatically displaying the CHILDREN Records (Many Sales)
Apr 11 2012, 12:38 PM
I have a whole bunch of forms, some for practice, some I thought were ok. Do you know which form you looked at?
Thank you for looking it over.
Apr 11 2012, 03:32 PM
The forms are the ones which run from your Main Switchboard
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here