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
do this?
