Creating a ID

  • Thread starter Thread starter terry birch-machin via AccessMonster.com
  • Start date Start date
T

terry birch-machin via AccessMonster.com

Hi all - Happy Monday!

I'm building a simple database in Access 95 - 97 to store patient
information leaflet records.

I need to create ID's for the authors, Printers and the Unit tables so what
i'd like to do is add "AU" in front of the autonumber that is the primary
key for that table. Ideally i'd like to create a 3 digit autonumber too. So
the result would be AU001 up to AU999, The prefix for the Printers will be
Pri - giving Pri001 - Pri999, and the unit prefix would be PIM giving
PIM001 - PIM999.

Firstly if anyone could enlighten me on the Unit ID - I.e. PIM<autonumber>
giving me PIM001 to PIM999 or even PIM1, PIM2, PIM3 and so on.

I know this must be simple but i've being at it all weekend and i've got
caffeine shakes and headaches!!

Happy Monday
Terry
 
hi
SELECT "AU-" & Right(Max([Id]),3)+1 & AS autoID
FROM yourtable;
this query will generate a auto number for you. however
with prefixes you would have to have a way to pre-decide
which prefix to use. a text box on the form would do.
then the query could be modified to this.
autoID: [Forms]![frmTest2]![text3] & Right(Max([Id]),3)+1
The first number in the table would have to be added
manually.
 
Hi all - Happy Monday!

I'm building a simple database in Access 95 - 97 to store patient
information leaflet records.

I need to create ID's for the authors, Printers and the Unit tables so what
i'd like to do is add "AU" in front of the autonumber that is the primary
key for that table. Ideally i'd like to create a 3 digit autonumber too. So
the result would be AU001 up to AU999, The prefix for the Printers will be
Pri - giving Pri001 - Pri999, and the unit prefix would be PIM giving
PIM001 - PIM999.

Firstly if anyone could enlighten me on the Unit ID - I.e. PIM<autonumber>
giving me PIM001 to PIM999 or even PIM1, PIM2, PIM3 and so on.

I know this must be simple but i've being at it all weekend and i've got
caffeine shakes and headaches!!
Click to expand...

Well... DON'T.

These ideas are very common but they're not wise. Storing two
different kinds of information in a field is neither necessary nor
good design. If you just want to *display* an AU before the name, just
use a Format property of

"AU"000

but don't waste storage space and complicate maintenance by actually
storing the letters AU in every record.

Secondly, don't use Microsoft's Autonumber datatype for numbers where
humans will be looking at them. Autonumbers ALWAYS have gaps; deleting
a record leaves a gap, but so does starting to enter an author and
hitting <Escape> to cancel the edit. Instead, use an Integer or Long
integer field and use a Form to do all your data entry (you *cannot*
do this in a table datasheet). In the Form's BeforeInsert event you
can put VBA code like

Private Sub Form_BeforeUpdate()
Me!AuthorID = NZ(DMax("[AuthorID]", "[Authors]")) + 1
End Sub

This will look up the largest existing numeric author ID, add one, and
store the result in the new record's AuthorID field.

You can do the same for the other units.

If you're storing authors and printers in the same table, this won't
work - but that would be a very strange design. If you are please post
back and explain.

John W. Vinson[MVP]
 
Back
Top