how to set autonumber primary key combining from several tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We have a database consisting of clients who have purchased different types
of insurance. I have set up a Client table, an Insurance Company table, and
separate product tables for each insurance product (ex: life, medical,
disability, Group Medical, Long Term Care, etc). From what I've studied, I
believe each product a client purchases should have a new unique primary key
number. How do I tell Access to use autonumber to assign the next
consecutive primary key number no matter which product table the product is
from? In other words, Primary Key #1 could be from the life table, and
Primary Key#2 from the medical table. Or is there a better way to do this?
 
If you are going to use separate tables for each product (do your products
really have that many things different about them?), be aware that you are
inviting intense maintenance requirements. ?Adding a new product? You have
to create the new table, modify any forms that use "products", modify any
queries that use "products", modify any reports that use ... (you get the
idea).

If you still have to use separate tables (I'm not convinced yet) for each
product, you could create a table that holds just a couple fields: ProductID
(your Autonumber) and ProductType. Then each of your separate "product"
tables (I'm still not convinced!) would use, as a primary key, a LongInt
field that holds the value of the Autonumber. This is considered a
one-to-one relationship, repeated again and again for each "product".

Did I mention that embedding data in tablenames (Product1, Product2, ...)
creates a maintenance nightmare...<g>?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
you could create a table that holds just a couple fields: ProductID
(your Autonumber) and ProductType. Then each of your separate "product"
tables ... would use, as a primary key, a LongInt
field that holds the value of the Autonumber. This is considered a
one-to-one relationship, repeated again and again for each "product".
Click to expand...

If the PRIMARY KEY designation is indeed to be used on the autonumber
column (I'm convinced that's not a good idea!) the OP would
additionally need a UNIQUE constraint (using a ' no duplicates' index)
on the compound of:

UNIQUE (ProductType, ProductID)

and in that order because if the OP does decide to 'promote' this key
to become PRIMARY KEY (as I would recommend) then it would yield
better clustering (physical ordering on disk) when compacted.

Each 'subclass' table would repeat the ProductType and REFERENCE
(Access Relationship with RI enforced) the 'superclass' Products table
using the (ProductType, ProductID) key. This is another good reason to
make the above key the PK i.e. it is the one used for foreign keys
(and will appear as bold text in the Access Relationships window as a
visual hint).

Additionally, each 'subclass' table would have a validation rule to
ensure the ProductType value matches the entity type for the table
e.g.

ALTER TABLE ProductDisability ADD
CONSTRAINT ProductDisability__ProductType__values
CHECK (ProductType = 'Disability');

Alternatively a column-level Validation Rule could be used but the aim
is to prevent the wrong product types being entered into each
'subclass' table.

Jamie.

--
 
You didn't tell us your business but it kind of sounds like an
independent insurance agency....

You probably need several tables before you're all done with your
application but IMHO you'll need at least the following to get
started:

tblClient
ClientID
ClientName - if individuals then a separate field for
each name
ClientNote

tblCompany
CompanyID
CompanyName
CompanyNotes

tblProduct
ProductID
ProductName
ProductDescription
ProductNote

tblClientCompanyProduct
ClientCompanyProductID
ClientID
CompanyNaame - Notice it''s not the ID
ProductName - Notice it''s not the ID
CCPNote

You can and have as many fields in the tables as you need to
accurately capture the relevant attributes of the entities.

It's possible to look at what you're sketched and declare it to be a
many-to-many-to-many relationship. I think it will be all of that and
that it will require further clarification of the real world facts to
know if the model is right (analysis). I doubt that all insurers
provide identical products across the board. Choosing a particular
company would limit which products might have been sold. Since you
would already know what you had sold you would make the correct
combination of selections for this sale to this customer but without
clarifying the model, the software couldn't help less knowledgeable
office staff limit their choices to only the correct possible choices.
If you write as badly as I often do that could be a problem. :-)

I would use autonumber Primary Keys throughout. I would treat both
tblCompany and tblProduct as simple lookup tables. I would use the
Form/SubForm paradigm to display and manage the data. The Form would
be based on tblClient. The SubForm would be based on
tblClientCompanyProduct. As suggested above, it departs the true
Relational model in that I would use the actual values of Company Name
and Product Name rather than their Primary Keys.. You may choose
otherwise but for getting started the explicit values are sometimes
very helpful. When entering a sale I would use comboboxes to select
the values for CompanyName and ProductName.

It's a bit intimidating getting going and it sure seems like a big
hill to climb. But when you finally get it going as well as you knew
it should when you began you'll feel like you can rule the world!

Jeff was pulling your leg and trying to get you to think through the
consequences of what you were considering. But if you haven't a clue
that there's a better way or what a better way might be, you go with
what you've got. Some people have separate tables for calendar years.
Others have tblFord and tblBuick.... They resist all attempts to
persuade them otherwise until you suggest a similar "smart" way of
doing that's outside their current problem scope. The year and car
people can quickly see the error of their ways when told it's exactly
the same as naming a table for each color they encounter, or person in
their database.

Access has a long, steep learning curve. The view is worth the climb.

HTH
--
-Larry-
--

westoaksins said:
We have a database consisting of clients who have purchased different types
of insurance. I have set up a Client table, an Insurance Company table, and
separate product tables for each insurance product (ex: life, medical,
disability, Group Medical, Long Term Care, etc). From what I've studied, I
believe each product a client purchases should have a new unique primary key
number. How do I tell Access to use autonumber to assign the next
consecutive primary key number no matter which product table the product is
from? In other words, Primary Key #1 could be from the life table, and
Primary Key#2 from the medical table. Or is there a better way to
Click to expand...
do this?
 
Jeff, Thank you so much for your detailed response. After reading your
info, I felt the need to explain: We are an independent Life Insurance
agency, and have been using pre-packaged client-management software designed
for this industry. When we upgraded to Vista (Feb 2007), this software no
longer functioned properly even in XP compatible mode (also had quirks
before), and we did not get any response from the company after leaving many
voice messages and emails. Also, this client info program requires a hefty
annual fee (due in Oct) and is encoded so that the it shuts down and will
only function with a new code supplied after annual fee is paid. Since the
program doesn't work with no prospect of when it will be fixed, there's no
point in renewing it, so we decided to convert everything to Access, which
would also give us complete control!

I was able to export the data in csv format from this program into XL, and
then convert from there into Access. I got the idea to use separate tables
for each product because the program was already set up that way. I believe
it may be because although all insurance products have some fields in common
(a policy number, an issue date, an issue age, product name, insurance
company), they also have different fields of info.

For example, just to show a few, a life policy has death benefit amount,
beneficiary names, cash value. A medical policy has deductible, coinsurance,
life maximum. Dental plans have different fields. There are also Group
Plans. A disability policy has monthly benefit amount, benefit period, etc.
If I were to put all these products into one table, I think there would be
too many fields and only some would apply to each product. I have deducted
from examining the prime ID keys from their tables, that each product was
assigned a new consecutive number (no matter which type of insurance it was).


Your explanation sounds very complicated and Jamie's response was similar to
yours, so I believe you that it's probably not a good idea. So with my above
explanation, can you provide any other suggestions?
Thank you so much for your time,
Lois
 
Larry, Thank you for your thoughtful response. You are exactly right-- we
are an independent Life Insurance agency. Your explanation has given me a
different outlook (I like your analogies) and perhaps I don't need all these
tables (but please see my response to Jeff--I'm not sure I can put all the
products in one huge table!).

I really appreciate your outline of how to set up the linking tables --it is
very helpful. My husband and I will be the only ones using this program, but
I still want it to be easy enough to fill in with correct info.

My dilemma is that there are so many different types of products and the
fields for each type of product are different, that I'm not sure if I could
use just one large product table if I want to include more detailed info on
each policy. But I will think about this more after your very insightful
explanation. Any other suggestions you provide will be greatly appreciated!
Thanks,
Lois
 
Back
Top