Table Questions

[g28dman]

Hello forum! Seems like this is the place to ask questions and get critiqued. I am starting a new project (seems right now out of my reach and knowledge, but am wanting to learn).

What I am buiding is a billing tracker for my insurance agency. What this is entails is simple policy details, whether invoice has been sent, customer paid, premium paid to company or if premium is carried on note either with agency or company, and what interest rate will be applied to that note. On premium the commission rate, commission earned, and if applicable the commission rate for sub agent and if they have been paid or not.

First of all I have a question about my tables, thus far and have read a little about normalization (sp?). What is required is 1-1 relationships should be kept to a minimal right?

So attached is a rough draft on my tables thus far...

Customers can have multiple policies (either by date,crop, or company) and each policy can have multiple details (commission rate).

I am looking for insight from someone, I can read answers all day long, but am looking for someone to say "Yeah your on track" or "why dont you try this".

The date fields, I would like to have a pop-up calendar picker. I have seen and downloaded many examples - How do I get the unbound forms to stick to my tables?

On this commission table..My thoughts are like the NorthWind example for orders sub form, my question on this how do you get the subform to start new when you want to put in a new order?

I am not looking for someone to build for me! Just give a some insight

Thanks to everyone!!

 

[Pat Hartman]

The relationship between customer and policy is 1-many, not 1-1 as your document said. I changed the primary keys, added foreign keys and removed some duplicate columns. I can't go further without more detail information. The commission table is probably wrong because you need to know which agent is being paid the commission.

Commission structures can be extremely complex. To represent one, you almost certainly need to have some table that defines the structure and some way to identify which commission rate each person associated with the policy will receive. Lots more work needs to be done in this area if you expect to calculate commissions.

I don't understand what the fields with the question marks indicate. I removed the question marks since field names should not include special characters. But, I'm also not sure that all those fields are needed and depending on what they are, you may need a separate table. In fact, if you want to track payments, you will need a separate table.

But, it's a start. Keep going

 

[g28dman]

Thanks Pat for your reply. the question marks are for me the represent a yes or no question.

On agency commission I would like to a decimal form field. And would only be a imput field. Same with subagent, each get a different amount and instead of trying to even more complicated from what I am doing just have imput field for a calculation.

Would you mind explaining foreign keys and how to set them up. I saw them on alot of examples and searched for them. What I found is just the term. I knew that's one thing I could've used.

On this commission table..My thoughts are like the NorthWind example for orders sub form, my question on this how do you get the subform to start new when you want to put in a new order?

I have found out how do this...just need to figure out how to tie it to the correct record

The payments table, i thought about that but didnt know for sure, since I have payments coming from insureds then payments going out to company. Alot of times we pay before we even get insured's check.

I can see this is going to be an uphill battle - but I am up for it. I appreciate you taking the time to answer my post.

 

[Pat Hartman]

Foreign keys are not something you specifically create. That is simply the name we use to refer to the field in the many-side table that is used to link to the one-side table. For example, you have a Customer table, an order table, an order details table, and a product table. CustID is the pk of tblCustomer, OrderID is the pk of tblOrder, OrderDetailsID is the pk of tblOrderDetails, and ProductID is the pk of tblProduct. tblOrder contains the CustID so you know who placed the order. tblOrderDetails contains the OrderID so you know what order these items are for. tblOrderDetails also contains the ProductID so you know which item is being ordered. tblOrderDetails is what is known as a junction table since it relates orderdetails to products.

In the relationships window, you would add all these tables and draw the appripriate join lines CustID in tblCustomer to CustID in tblOrder, OrderID in tblOrder to OrderID in tblOrderDetails, ProductID in tblOrderDetails to tblProduct. For each relationship, you select the enforce referential integrity option. This tells Access that you don't want to allow orders without customers or details without an orderID, etc.

When building relationships, in all cases, you are drawing a line from the primary key in one table to a non-key (usually) field in another table. It is the non-key field in the "other" table that is referred to as the Foreign Key. Essentially a foreign key is a copy of the value of a primary key from another table and is used to link to that other table.

Now, of course primary keys may be comprised of more than one column and so foreign keys may also be comprised of more than one column. The above description was as simple as I could make it. Foreign keys may also be a part of the primary key of the table in which they are placed or in the case of a one-to-one relationship, the primary key of one table is related to the primary key of another table. So, this whole relationship business could get complex. But, for the most part, the pk will be a single column and so the fk will also be a single column.

 

[g28dman]

Thanks again Pat...I really thought I was missing something.

I will make an effort on this, this weekend. I really dont like be in the dark on this kind of stuff, I usually spend my nights and weekends slaving over one thing, then moving to the next and dont have any formal schooling.

My wife says I spend to much time in front of a computer, says I am in wrong biz and that if this is something that drives me so much maybe I need to go back school and learn and change careers.

 

[g28dman]

Sample DB

I have reworked my tables and relationships, attached is my database. I hope I am on the right track. Would someone mind looking at it and telling me either way?

Thanks

 

[Pat Hartman]

tblPremium and tblPremiumCompany are still not correct. PrimaryKey is not a good choice as a name. Use the name of the table instead such as PremiumID and PremiumCompanyID.

You also need to enforce referential integrity for the joins.

Why is there a 1-1 relationship between the policy and the premium tables? I would think that most policies are paid annually or in installments so that if you are recording when something was paid, you'd need the ability to store multiple records.

I also don't understand the data in the premium and premium company tables well enough to comment further.

 

[williams9969]

Reduntant

Also-

In your table I see phone number two times...your table is not at all normalised.

you need to add another table for your phone numbers

PhonenumbertypeID(PK)
PhoneNumberType(FK) to your main table
PhoneNumber

There are numerous other things I could go in to...same theory would apply to your address, etc

DW