Help with new database project and primary key client ref

[Colm O'Brien]

I'm only a beginner, but have an ambitious project. I need to create a
database with several tables, Tblclients holds personal details of clients
(fname, midname, surname, dob, sex, marital status, client status, related)
Tbladdress holds client's current and previous addresses Tblpolicy holds
details of clients policies (Company, Policy No, Sum Assured, premium,life
assured,) these are not all fields but re the most important at the present.

I want the client ref to be created from the clients surname, forename and a
two digit no.(preferabl auto generated & automatically checking if the
rference already exists and if so displaying the current record to check if
this is a new record or duplicate) I know people will say that if client
marries client ref will be wrong but this is to fall in line with a current
paper based method.

that's the first problem. The second is how to query the client data base
and display the name of the client and any related client. related in the
table stores the reference of the related client.

I know there's a lot in this maybe this is the wrong forum if so please
advise where best to ask.

Thanks in advance

 

[Jeff Boyce]

If you are designing your table structure to "fall in line with a current
paper based method", you are shortchanging yourself and Access. Access is a
relational database, and you won't get the best use of its features and
functions if you start out designing from a paper method.

And you don't need to! If you create and populate well-normalized tables,
you can use queries to join the data together for reporting and display
purposes.

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Colm]

Jeff maybe i wasn't clear its only the ref that i want to retain from the
paper based method. as clients paper files are referenced in this way. eg
obco01 would be my reference and boje01 would be your reference.

 

[Larry Daugherty]

That would be a giant step in the wrong direction. What you are
trying to create is called an intelligent key - it's not very
intelligent and not a very good unique identifier either.Incredible
though it may seem, your scheme won't get you very far. How do you
intend to handle the inevitable collisions with names that reduce to
the same root?

Read a book that touches on normalization of your data. If you can't
find one right away you might start with the "Ten Commandments" at
www.mvps.org/access

If you need to create a sequence, *do not use the Autonumber
datatype*. Autonumbers exist simply for use as surrogate keys. They
are guaranteed to be unique but not necessarily sequential.

If you need to create a sequence, look into using "DMax() +1" Even
then, don't try to prefix or postfix initials or other separators in
the same field. If you need to see an intelligent key you can perform
the concatenation of two or more fields for purposes of display.

Post back as specific issues arise.

HTH