Sequential numbers

[jules]

I have to create a database of our clients. They each have their own file
number, i.e C1234. From reading all the other posts I understand that I am
not supposed to "mess" with the autonumbering so can anybody tell me a way of
creating a column where I can put the first file number, say C1234 and it
will increase by itself to C1235. Also, as the database will hold a real lot
of information I was thinking to create two tables and link them, so would
this C1234 number be the link? Please excuse the terminology, am VERY new to
database.
Thanks in advance

 

[Tim Ferguson]

can anybody tell me a way of
creating a column where I can put the first file number, say C1234 and
it will increase by itself to C1235.

First: what is the meaning of the "C"? If some of your records are also
D's and F's and M's, and if that letter means something (language? city
of origin? year of entry?) then you really need to be storing this
identifier in two fields, one for the letter and one for the number. It's
easy to present them as one string to the user, but you will come unstuck
if you try to manage two bits of information in one field. Search for
"intelligent key" for further information.

On the other hand, if the "C" is constant and has no meaning, then don't
store it at all. Just use a format on the text boxes on forms and reports
like "\C000" and the C will magically appear at the front of your numeric
identifier for all to see.

Second: how exactly do you want this number to behave? Do you care about
missing numbers in the sequence? What happens when one gets allocated and
then deleted? When do you want to allocate it -- at the start of creating
a record (likely to get missing numbers if the record is aborted) or
right at the end when the record is definitely going to be saved (makes
it unavailable for linking records to it from other tables)?

Also, as the database will hold
a real lot of information I was thinking to create two tables and link
them, so would this C1234 number be the link?

Hold on there! -- the reasons and methods for creating tables have
everything to do with the meaning of the data and how they relate to each
other, and nothing to do with the amount of information. A small database
might need a dozen tables or more if the model is really complex: a huge
database might only have a few tables if it needs a simple design. The
links between the tables depend on what the link is for and how it is to
behave.

If you are able to post back again with more details of what you are
trying to achieve, we may be able to provide some more detailed pointers.

All the best


Tim F

 

[jules]

Hi thanks for your reply. The numbering sequence for our files goes from 0 -
999, with the letter at the beginning. Currently it stands at C500+ so i
guess after another 500 files we will move on to D so i guess I do need the
letter. They get allocated this number when we first open the file. The
records will never be deleted, they will just have a date assigned to them
under a column "file closed".
My employers deal with insurance companies so the information which needs to
go into the database would be eg file number, insured name, insurer name, the
name of the assigned fee earner, date file opened, closed, billed etc. On
another table (if necessary) i would have more details about the particular
case, i.e accident location, police, etc and hopefully link them through the
file number. We could then sort for example for all cases where the accident
occurred in a particular location, or from a particular insurance company. I
hope I'm not rambling too much. It was only last week I was assigned this
task and i have never worked on access before!!! You are dealing with a
complete and utter notice so excuse my ignorance.
Best wishes and thanks for your help
Jules

 

[Jeff Boyce]

Pardon my intrusion...

If this is also the first time you've ever worked with a relational
database, I urge you to step back from your immediate assignment and learn
about normalization and relational database design. Access is a powerful
tool, but you will only be able to use its real power if your table
structure is well-normalized.

As a (very) small example, you mentioned that your customers use a
"numbering" system that starts with a letter and concludes with up to three
digits. Instead of starting out designing your database/table to use that
information as a primary key, you could consider that a pair of "facts"
about records, just like a name or address.

A scan through this newsgroup (tablesdbdesign) will reveal a consensus that
a good place to start is to turn off your computer and take up paper and
pencil. First jot down the entities and relationships that represent your
situation ... and if these terms are unclear, spend some time learning about
normalization!

Best of luck on your project!

Jeff Boyce
<Access MVP>

 

[Tim Ferguson]

Everything Jeff said, and a few specifics to add:-

Hi thanks for your reply. The numbering sequence for our files goes
from 0 - 999, with the letter at the beginning. Currently it stands
at C500+ so i guess after another 500 files we will move on to D so i
guess I do need the letter.

I have to say that this sounds like really unnecessary complication: if
D001 is exactly the same as C1001, then FGS just carry on numbering them
with numbers. "It's staying like that because it's always been like
that" is probably a good reason for getting your CV up to date...

The records will never be deleted,

Yes they will: there will the time when Ziro Zirabander gets a new file
created twice, and one of them has to be deduplicated. Then there will be
the time when a file is opened all the way to the signing of the cheque,
and the customer will back out. Etc etc etc. There are _always_ deleted
files.

needs to go into the database would be eg file number, insured name,
insurer name, the name of the assigned fee earner, date file opened,
closed, billed etc.

There are at least three tables here: InsuredPeople, InsuringAgencies,
Files, Bills etc.

On another table (if necessary) i would have more
details about the particular case, i.e accident location, police, etc
Claims

and hopefully link them through the file number. We could then sort
for example for all cases where the accident occurred in a particular
location,

Table of Locations then...

It was only last week I was assigned this task and
i have never worked on access before!!! You are dealing with a
complete and utter notice so excuse my ignorance.

This is not to insult your intelligence or your ability: but you must
have a realistic understanding of your level of knowledge. Your company
seems to be betting its business future on someone who does not know what
she is doing -- and at a guess, no-one looking over you who knows if you
are doing it right or not. Is this a good business plan? I would guess
that you have a good deal of learning about database design, systems
analysis, user interfaces and so on, before even starting to "work on
Access". Jeff is right: take a long step back and plan a realistic
strategy.

Sorry!
All the best


Tim F