0

I am looking for some design techniques to achieve the following:

A 3-part serial number that is generated upon record entry. Format Example: 25-001-14

The number is used to track yearly records from various locations.

The first part states the location the record is associated with, this would be a user input during record creation. The second part is the record number, I would like for this to be automatically generated, but needs to be sequential and separate for each location and needs to reset each year. The third part is the two digit number for the year the record was created in. I would like this to be automatically generated if possible. Note: I am currently not concerned with when this cycles back around and I face redundant data issues.

I'm thinking I would like records to be stored in multiple tables that are separated by location, if this would help things!

Any ideas would be greatly welcomed.

dsolimano
  • 8,870
  • 3
  • 48
  • 63
FGLC0983
  • 93
  • 1
  • 1
  • 8

1 Answers1

0

I think I would use 3 key fields - one field each for location, record and year. The location and year fields would be created when you get the input to create new records. I would set up a query to find the last record number used by location and year and use that query to assign the new record number when you create a new record. The concatenation of the 3 fields would be the key you described.

With a key field for location, separate tables are not necessary unless that's useful for other reasons. I would probably use just one table - you can always filter the records by location anytime you need to.

Don George
  • 1,328
  • 1
  • 11
  • 18
  • I like the idea, would there be a way to have the query automatically run, or would that have to be manually done? – FGLC0983 Mar 25 '14 at 17:12
  • How are you populating the database with new records? Whatever creates the record (form, action query, vba script, etc) should have access to the query and be able to determine the next record number and add it to the record when it is created. – Don George Mar 25 '14 at 20:24