autonumber does not recognize deleted records

[Guest]

 

[Douglas J Steele]

Well, you've neglected to post a question, but from the subject of your post
I'll guess that you're commenting on the fact that when you delete a record,
the value of the Autonumber doesn't get reused.That's absolutely correct,
and that's how it's supposed to work. In fact, if you start to insert a
record and change your mind and cancel, the number that would have been used
for the record is lost.

Autonumbers exist for one purpose only: to provide a (practically
guaranteed) unique value that can be used as a primary key. It doesn't
matter whether the values are 1, 2, 3 or 3, 5, 9. In actual fact, it's rare
that the value of the autonumber is presented to the user.

If the value of the Autonumber field is important to you, then you probably
shouldn't be using an Autonumber.

 

[Guest]

We are an architecture office. We give each project a unique code. Such as
RS0324FR2004D. RS= RESIDENCE , 0324= NUMBER OF PROJECT, FR = COUNTRY CODE ,
2004 = YEAR , D = PROJECT STATUS.

I have a separate column for each one of this information.

1. How do I auto refernece all this information to a single column? (in
excel is simple)
2. Presuming i dont need the autonumber to ajust on deleted records how do I
construct a column that autonumbers the projects even if some are deleted?

 

[Joseph Meehan]

We are an architecture office. We give each project a unique code.
Such as RS0324FR2004D. RS= RESIDENCE , 0324= NUMBER OF PROJECT, FR =
COUNTRY CODE , 2004 = YEAR , D = PROJECT STATUS.

I have a separate column for each one of this information.

1. How do I auto refernece all this information to a single column?
(in excel is simple)
2. Presuming i dont need the autonumber to ajust on deleted records
how do I construct a column that autonumbers the projects even if
some are deleted?

I am not sure I fully understand your question, but you can use a
formula to put all those individual fields together for display. For
example [RESIDENCE]&[NUMBER OF PROJECT]&[COUNTRY CODE]&[YEAR]&[PROJECT
STATUS] would give you the result you want. You can copy that formula to a
query by putting something like:

UNIQUECODE: [RESIDENCE]&[NUMBER OF PROJECT]&[COUNTRY CODE]&[YEAR]&[PROJECT
STATUS]

Or you could use the same code in a control in a report or form.

If the missing numbers is a problem, then you could make your own number
generator.

Note: a true number does not have leading zeros. So 0324 would be
stored as 324 if it is stored as a number. If it is stored as text then it
may be 0324 and is not subject to being used in a calculation like
1+0324=0325.

 

[BruceM]

I think you are talking about a sequential number for the project number.
In that case you could take a look at the sample database here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
That would probably be your primary key, assuming the sequence doesn't start
over each year or something like that. Once you have that you can combine
the fields into an unbound text box on your form or report, or in the
underlying query as Joseph has suggested. I will assume that when you start
a new project you would choose residence, country, etc. from combo boxes or
list boxes that are bound to appropriate fields in the underlying table.
Those fields, together with the project number, can be combined for
viewing, but the combined value should not be stored. Relational databases
are very different that spreadsheets in that way. You will be viewing
and/or working with the data in a form or report, not directly in the table.
A table resembles a spreadsheet, but you cannot think of it as one. It is
just a container for information. As has been suggested, you can combine
(concatenate) the fields as needed.