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!!

 

[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.

 

[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

 

[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