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: