Primary Key for Customers

Hi

I am interested in using Access to manage my company data hence i am learning now by reading Dummies book. Now, i have the following issue and hope someone can advise me how to go about it.

My company sells cars and provides repaired and other misc services.

I try to keep track of the debtor and its ageing. Hence, currently i try to create a customer listing in Access first and slowing progress to debtor ledger.

In my debtor ledger, i am thinking of splitting into three group for my debtors/customers. ie debtors for car, debtors for services, debtors for used car. As such, how shall i design my customer listing?

As the same customer can have its name on all these services. so shall i have assign three primary key for all customers?

I am a bit loss. Thanks for guiding me in advance.

I would store all customers in the one table. I would then have a sub table in which you store the customer type.

Your table structure might look something like;

TBL_CustomerDtl
CustomerID (PK) autonumber
CustomeName
etc.

TBL_DebtorType
DebtTypeID (PK) autonumber
DebtorDescription

TBL_CustomerType
CustomerID (FK)
DebtTypeID (FK)

You would then have your main form with all you customer details on it, with a Sub_Form that populates the TBL_CustomerType the two forms would be linked via the CustomerID with a combo box populating the DebtTypeID

Hi John,

Thanks for your reply.

You suggest autonumber. I wonder can the autonumber be changed in future?

Harrold said:

Hi John,

Thanks for your reply.

You suggest autonumber. I wonder can the autonumber be changed in future?

Click to expand...

How do you mean

For example,for customer ID i use autonumber now for its primary key. But i would like to change it in future, is it doable?

Harrold said:

For example,for customer ID i use autonumber now for its primary key. But i would like to change it in future, is it doable?

Click to expand...

Pretty much anything is doable. More importantly the deveolper needs to realise that like many choices they make there are pros and cons to each alternative.

There can be good reasons to use the autonumber PK anyway even if you do later come up with a unique number that could identify the person. There is no reason you couldn't prepare for the future and have both right now.

Moreover this scheme allows the non-PK unique identifier to be easily changed at any time without having to find every change that might result throughout the entire project.