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:

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.