I have a database in Access 2007 which I need to auto increment within a defined range, 001 to 899, ie., the batch entered at noon on 4/5/2012 will automatically be given the batch number 742. The next batch should automatically be assigned 743. In addition, I need to be able to keep each instance of 742, unique.
For example, the batch number 742 on 4/5/2012 has financial transactions totaling $34,167.22, but batch number 742 later in the year has transactions totaling $767,099.55. When someone searches for batch 742, I want to the reports for both batches to come up so that the user can choose which batch is needed.
Previously, I have archived the data as soon as the batch numbers are close to being re-used, but with more and more batches being input, this is no longer practical.
Any ideas.
For example, the batch number 742 on 4/5/2012 has financial transactions totaling $34,167.22, but batch number 742 later in the year has transactions totaling $767,099.55. When someone searches for batch 742, I want to the reports for both batches to come up so that the user can choose which batch is needed.
Previously, I have archived the data as soon as the batch numbers are close to being re-used, but with more and more batches being input, this is no longer practical.
Any ideas.