Giving records in MS Access unique numbers

[antonyeo]

I have a table. I will add thousands of records to this database each year. It’s essentially needed to maintain inspection records. Each problem found get it’s own record. Its possible to go into a building and come out with 20 or more problems (20 new records). Records older than 3 years I could legally delete, so unless access has a limit on number of records I could use one database. Ok the real problem is this.
When we find a problem it goes into the database. I need a means to identify any single record and the best way would be to give each record a unique number. That unique number would be great if it actually meant something instead of just having a random number. Ideally the number will be a 12-digit number (was 10 digits but if I have one database I should include a two digit year in that number to eliminate duplicate numbers).

So, when I come to my database to enter a record it would be great if after I enter the building number and date it automatically created a unique record number. That 12-digit number would be represent as follows:

First 4 digits a building (no building number is greater than four digits) Example 2525

The Next 6 digits represent the date (ddmmyy format) Example 240804

Next two digits represent a normal two digit numbering succession. Example 01, 02, 03

I would be better if I could get that number to look like this 2525-240804-01…, my people could look at this number and know right away it was building 2525 on 24-Aug-04 and 01 problem.

Also if I go to a different building the succession of number (at the end) starts at 01 again. So in the same table I could have the numbers 2525-240804-01 and 0001-220804-01

I simply do not know how to get this to happen. I have all other sections of this database operational this part I can’t do. Looking for directions on exactly how to do this! :eek:

[Mike Feury]

I would suggest letting Access do its own thing with an auto-number primary key. Your next three fields are date, building# and problem#.

For your staff to enter their inspection data, use a nested form. The main form would have date and building#, the sub-form would have problem# and the other fields.

In the forms and reports to answer queries and display data to your staff, you would then typically list these three fields first.

I feel that's a better approach than combining those three fields into one 12-digit field. This will allow you to call up all the data for a building or a date for example, which you can't do with the 12-digit.

As a general principle, it's best to store data in its smallest logical form. Names for instance are generally split into first and last names, otherwise you couldn't sort by last name.