Microsoft Access

[TURNIP]

Hi, I am new to access and trying to set up a database, basically I want to
store a customer with details including address,contact details etc and then
I have three or four product options which I will be completing depending on
what they buy, could be one could be all four.

I was setting this up as 5 tables and trying to create a relationship based
on 1 primary ID (customer ID) but I cant get this to work, I can enter
details in the customer form I set up but then when I move to the other forms
I have set up as per the product tables it does not seem to link back to the
specific customer, anybody got any ideas how I can achieve this or a better
way to do it??

 

[John Vinson]

Hi, I am new to access and trying to set up a database, basically I want to
store a customer with details including address,contact details etc and then
I have three or four product options which I will be completing depending on
what they buy, could be one could be all four.

I was setting this up as 5 tables and trying to create a relationship based
on 1 primary ID (customer ID) but I cant get this to work, I can enter
details in the customer form I set up but then when I move to the other forms
I have set up as per the product tables it does not seem to link back to the
specific customer, anybody got any ideas how I can achieve this or a better
way to do it??

What are your tables? If you have a different table for each "product
option" you're probably on the wrong track. In any case, the related
tables should NOT have the CustomerID as the Primary Key - each such
table should have its own primary key, and a different field
CustomerID as a "foreign key" back to the Customer table.

Typically you would use a Form based on the Customer table, with one
or more Subforms based on the related table(s). Using the CustomerID
as the master/child link field will keep the tables in synch.

John W. Vinson[MVP]

 

[TURNIP]

Thanks John, this goes someway to getting me there.

I had tables for each product option like you say, the first table was
details of mortgages customers bought, the further tables were if they bought
associated life cover, buildings and contents cover etc and I also wanted a
further table to track the progress of each product, i.e. has the mortgage
completed yet etc, does htis give you any more insight?

 

[John Vinson]

Thanks John, this goes someway to getting me there.

I had tables for each product option like you say, the first table was
details of mortgages customers bought, the further tables were if they bought
associated life cover, buildings and contents cover etc and I also wanted a
further table to track the progress of each product, i.e. has the mortgage
completed yet etc, does htis give you any more insight?

A little bit. I would guess that a customer might obtain a mortgage,
and then might or might not buy life insurance and the other types of
insurance? If so, you may indeed need separate tables for the separate
products, since each purchase is (more or less) independent of the
other purchases.

If you have a suite of (say) life insurance policies with different
terms, then you probably have a many to many relationship. A given
customer might buy one or more insurance policies (one for herself,
one for her husband say); and a given policy type might be bought by
many customers. If so, you'll need one (rather small) table of
Policies - what you have for sale; and a second table of PoliciesSold,
with the CustomerID as a foreign key, the PolicyID as another foreign
key to the Policies table, and other fields for that particular
person's details of coverage.

The Progress table would indeed be a separate table, but I don't
really understand what "progress" you're tracking. What are some of
the kinds of information that you want to track?

John W. Vinson[MVP]

 

[TURNIP]

thats great John, the progress table would just be to track the progress of
each case, for example customer 1 bought a mortgage and 2 life policies, has
the mortgage completed and have their life policies been underwritten, are
they on risk now etc.

If they took out a mortgage with say a two year dicount on repayments, when
will this be up etc.

 

[John Vinson]

thats great John, the progress table would just be to track the progress of
each case, for example customer 1 bought a mortgage and 2 life policies, has
the mortgage completed and have their life policies been underwritten, are
they on risk now etc.

If they took out a mortgage with say a two year dicount on repayments, when
will this be up etc.

I guess the question is: To which table does this progress table
relate? I would guess to the customer - but also to all of the policy
tables in some way.

Might it not be better to just have fields in the policy tables, to
indicate that this particular policy has been underwritten, and this
mortgage has had so many payments, or is paid off? You could then use
a Query to search for all not-yet-underwritten life policies, etc.

John W. Vinson[MVP]