Autonumbering question revisited
Chet
Van mentioned that I should reconsider the table design. Let me briefly
explain why I did what I did. The database tracks prospects. In addition,
it tracks what type of insurance policies the prospects have purchased.
Each type of policy has its own table because most of the info tracked is
specific to that type of policy. The primary key for each table is the
ProductID number which is set to autonumber. (I can't use policy numbers
because they are not released immediately.)
So far I have not had any problems but I am trying to bring all of the
different policy information together on a report and I realized that
different policies will have the same productID numbers and that may cause a
problem. What I would like is for the autonumbering to pick every 4th
number or to start at 1,000 instead of 1. Which is a better approach? The
help files made the second option seem pretty difficult.
I am still very new to Access so any advice is appreciated (I have at least
learned that their is always a better way).
TIA
Steve Glynn
If I could but in here -- subject to correction from Van, Michael, and folks
who know far more about this than do I -- I think you need the following
table designs:
tblCustomer
CustomerID (autonumber)
CustomerName
CustomerAddress
CustomerDateOfBirth
tblPolicies
PolicyId(autonumber)
PolicyName
PolicyDescription
PolicyTerm
tblCustomerPolicies
CustomerID (points to [tblCustomer].[CustomerID]) )Joint
PolicyID (points to [tblPolicies].[PolicyID]) )Primary Keys
DateTakenOut......
Steve
"Chet" <Xchett...@memberbenefits.com> wrote in message
news:3uJ66.166$u55....@news4.mco...
Albert Marshall
efits.com> writes
>Thanks for the responses Van and Michael,
>
>Van mentioned that I should reconsider the table design. Let me briefly
>explain why I did what I did. The database tracks prospects. In addition,
>it tracks what type of insurance policies the prospects have purchased.
>Each type of policy has its own table because most of the info tracked is
>specific to that type of policy.
Then you could still have a main table that stores the basic information
(policy type, date, company, policy No, possibly premiums and commission
levels) and a set of "sub-type" tables for the details that change for
each type of policy. Then you link the sub-type tables to the main one
using the main table's autonumber PK.
This will make reporting much easier because often you will only want
information like "how many policies has Mr Jones bought?" or "Who hasn't
bought a policy in the last year?"
I have a similar set-up where I need to track lots of very different
components used in making a product. They have between 3 and 12 fields
but they all have a name and a part number.
> The primary key for each table is the
>ProductID number which is set to autonumber. (I can't use policy numbers
>because they are not released immediately.)
>
>So far I have not had any problems but I am trying to bring all of the
>different policy information together on a report and I realized that
>different policies will have the same productID numbers and that may cause a
>problem. What I would like is for the autonumbering to pick every 4th
>number or to start at 1,000 instead of 1. Which is a better approach? The
>help files made the second option seem pretty difficult.
It's a helluva lot easier than trying to use every 4th number (and then
you start selling a fifth product type).
>
If you still want to keep the tables completely separate you could use a
two-field primary key. Set up a PolicyType field in each table, and set
its default value to a code for that table (1 for life, 2 for accident
...). Then you select both that field and the autonumber and click the
PK button.
--
Albert Marshall
Van T. Dinh
method (using 2-field PK) is something new for me as well. Personally, I
still go with the 1st method as most likely Union Queries will be required
later with the 2nd method and Union Queries with different Fields are not my
favourites.
While you at the MS Download centre for the Relational Database Design (link
in the previous post), you may like to go to the Knowledge Base in Microsoft
site to search for the Article Q100139 "ACC: Database Normalization Basics"
to give you a bit more info. about Database Design.
HTH
Van T. Dinh
"Chet" <Xchett...@memberbenefits.com> wrote in message
news:3uJ66.166$u55....@news4.mco...
Van T. Dinh
(More of a discussion rather than a correction. I am learning as well)
1. I incline to think your tblPolicies should be more of a Policy Type
Table which is more or less a "LookUp" Table for sort of "general" details
about this Type of Policy.
Actually, reading you post again, I think you actually meant Policy Type as
you actually have DateTakenOut in the tblCustomerPolicies.
2. (If I am right that you think of Policy Type) as in 1, I would go for an
AutoNumber PK rather than the 2-Key Primary Key. The reason: (If I were
rich and I owned 5 mansions!!!), I may have 5 Home Building Insurance
Policies + 5 Home Contents Insurance Policies for the 5 mansions. Your PK
will restrict me to just 1 Home Building Policy & 1 Home Contents Policy.
The alternative is 3-Field PK (with the 3 is just a sequencing number) but I
think it is easier to handle this with AutoNumber rather than a 3-Field PK.
3. I would go for something like this:
tblCustomers
CustomerID AutoNumber PK
(similar Fields)
* tblPolicyTypes (more or less a LookUp Table)
PolicyTypeID AN PK
PolicyName
(similar Fields)
* tblCustomersPolicies
CustPolID AN PK
PolicyNo Text (this is the policy number Chet
advised not avail.
on proposal & well after
acceptance)
frg_CustomerID Long Foreign Key linked to tblCustomers
frg_PolicyTypeID Long Foreign Key linked to tblPolicyTypes
ProposedDate
AcceptedDate Date/Time
EffectiveDate Date/Time
InsDuration Date/Time
Premium
DatePaid
ReceiptNo
....
CPNotes Memo Notes for this policy (e.g. customer is
deaf)
* tblsubCustPolDetailsA/B/C... (a number of these Tables) to store details
specific to a particular insurance policy which depends on the PolicyType as
per Chet's requirements and Albert's 1st solution. Fields:
CustPolDetID AN PK
frg_CustPolID Long Foreign Key linked to CustPolID
Other Fields different from Table to Table
(and this is where I cannot be any more specific & I am too sure what's I am
doing either. I never had a case I have to use this but this way sounds
logical)
As you would notice, I use AutoNumbers everywhere. I am sure lots of people
will disagree and prefer to use a more natural key!!
4. I am going to throw a spanner in the works:
Going back to my 5 mansions, all the Policies were taken out in my wife &
my Names. On top of that, I have a Health Cover for my Dog in my name only
(my wife hates dogs and refuses to have her name in this policy) and my wife
has an Health Cover for her cat (I hate cats ...).
Do we have 3 CustomerIDs (1 me, 1 my wife, 1 combined) or what???
Cheers
Van T. Dinh
"Steve Glynn" <steve...@lineone.net> wrote in message
news:EoL66.16925$O83....@news6-win.server.ntlworld.com...
Steve Glynn
hiding them from the user when necessary) is a better way to handle this,
since it cuts down the risk of operator error.
As to your "spanner in the works" about policies held jointly by you and
your wife and policies that you or she hold individually ... Eeek! I used
to work for a company that makes banking software, so this was quite a
common problem. I wasn't involved in that part of the coding, so I don't
know the detailed solution, but we worked on the principle that individual
customers are discrete entities, as are accounts, and that one customer can
have many accounts and one account can belong to many customers.
I guess the solution was to have a linking table that matched up customers
and accounts, in the way that an OrderItems table will match up Orders and
Products, and then to write a query that retrieves Customer details, all
accounts associated with the customer, and details of other customers (if
any) associated with the account.
I know that the tool the company sold, which enabled the operator in the
bank's call centre to jump between accounts and associate and disassociate
them with customers at the click of a button, involved yards of SQL and C++
and cost sacks of money! Though, to be fair, it was doing a lot of other
clever things in the background, such as searching for related accounts if a
particular account went overdrawn without agreement, honouring the cheque if
there were sufficient funds somewhere in the related accounts to cover it,
and firing off a warning letter to the customer with some suggestions about
which accounts between which he might want to transfer money .
Steve
"Van T. Dinh" <VanThi...@bigpond.com> wrote in message
news:#yTCdQseAHA.896@tkmsftngp04...