Primary Key/Foreign Key

[TURNIP]

I have created a database with a few tables which are mainly one to many
relationships with a primary key in one table and a foreign key in another
table providing the link. I want to prepopulate the foreign key field in the
linking tables with the unique primary id from my first table, can this be
done and if so how?

 

[TURNIP]

Thanks Karl, I do have about five tables which are linked to the one main
table which has customer ID as a primary key and this is contained as a
foreign id on all other tables, should I still create sub forms to deal with
this?

 

[KARL DEWEY]

Sound right. Post your table structure and I'll check to see if I would do
something different.

 

[TURNIP]

Thats brilliant Karl thanks the way I had it planned was something like the
following:

tbl customer
tbl customer.customer ID
tbl customer.customer forname
tbl customer.customer surname
tbl customer.customer DOB
tbl customer.customer address
tbl customer.contact numbers

tbl life
tbl life.life ID
tbl life.customer ID
tbl life.product
tbl life.term
tbl life.sum assured
tbl life.premium

tbl mortgage
tbl mortgage.mortgage id
tbl mortgage.customer id
tbl mortgage.lender
tbl mortgage.amount
tbl mortgage.term
tbl mortgage.completion date

tbl insurance
tbl insurance.insurance ID
tbl insurance.customer ID
tbl insurance.provider
tbl insurance.sum assured
tbl insurance.premium

tbl status
tbl status.status ID
tbl status.customer ID
tbl status.commission
tbl status.application completed
tbl status.underwritten

As you can see I need to build details of the customer who can then buy any
combination of three products mortgage,life insurance and buildings and
contents insurance so the product tables need to link back to the customer
table not necessarily each other, or do they?? any recommendations on best
way etc?

 

[KARL DEWEY]

Would this capture all of your information? --
tblCustomer --
CustID
LName
FName
DOB
Addr1
Addr2
City
ST
Zip
Phone
FAX
E-mail

TblProduct --
ProdID
Title


TblCustProd --
CustID
ProdID
Status
term
sum assured
premium
lender-provider
amount
completion date
sum assured
premium
commission
application completed
underwritten

 

[TURNIP]

Yeah I suppose so, is this basically one many to many relationship??

in the tblcustprod there would have to be a few sum assured, provider and
premium fields etc, would this create a problem or would that be ok providing
i numbered them??

 

[KARL DEWEY]

You would have many CustID - ProdID combinations.

If you are going to have multiple mortgages with the same customer then add
another field to list them separately.

 

[TURNIP]

Karl thats great, really helpful cant tell you how long I have been pondering
this and trying to get it working.

Thats the table side of things sorted, there is probably going to be about
half a dozen users for this and intended to create forms for data entry etc,
would you still use a sub form for this set up?

also when you say add another field for multiple mortgages how do you mean?
apologies for all the questions I am just extremely new to access and still
trying to undertsand the principles etc.

again you have been really helpful and if there is someway I can provide
feedback for you etc please let me know.

 

[KARL DEWEY]

would you still use a sub form for this set up?
Yes, Customer in main form and product-item in subform.
Whatever label you want to put on it for the following combination
First record --
CustID - 45
ProdID - Mort
Item - 1

Next record --
CustID - 45
ProdID - Mort
Item - 2