How can I enter unique numbers based on date/number on dat in MSAccess 2010

By wurthjt ·
I need to automatically enter a unique date on a report (an order form) which incorporates today's date and a unique two-digit identifier, which resets when the date changes, eg on 25 July 2011 I might issue three orders, the last being 11072503. On 26 July I need to start at 11072601. Putting them in manually is not an option as several people will use the database and will not necessarily know what the last person has done. Whatever help you can give would be much appreciated - I am not an experienced database designer.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

It can be but and experienced designer would tell you not to

by Tony Hopkinson In reply to How can I enter unique nu ...

Intelligent numbers are a major pain and keying your database on public keys is paving stone one on the road to ****.

Depends on whether you need to deal with cancellations.
Have an identify field as the key for the orders table, this is a surrogate key ie you should not expose it to the user. Another field for the date.
The next number you need is
is select (Isnull(Count(*)) From Orders Where OrderDate=?.
Manually has nothing to do with the multi user problem. If Fred and Bill are both entering an order at the same time, your get next number function needs to be atomic with adding an order.
ie in the above implemenation when you do the select count and there were three orders already that day do it wrong and they will both get numbered four.
This sounds lile it's some hold over from a previous sytem, possibly manual, evaluate whether it's necessary any more, getting rid of it will save you a world of hurt.

Collapse -

Another way to put it:

by techrepublic In reply to How can I enter unique nu ...

OrderIDs need to be unique and date+serial# used to be a fine way to do that; not, though, in the environment you describe. The problem is that Access has no mechanism for being SURE that 2 of your users aren't grabbing the serial# at the same instant.

The best solution is to let Access create unique OrderIDs as it creates the Order record and use those as the order number on your form. They'll be unique numbers increasing by one for each order from now 'til doomsday (epoch of your choice). Keep a separate field for order number but you can populate it from the OrderID. (Why? because some idiot is going to require a exception of some kind to how order number looks and if it's inseparable from the key it'll bite you down the road)

If there's some external good reason the order number MUST contain the date, keep the OrderID mentioned above and print an order number composed of the date and the last n digits (in your example, n=2) of the OrderID. Choose "n" based on the maximum number of orders in a day. Hint: you WILL overflow eventually; make "n" bigger than you think is reasonable.

More complicated (but looking like what you describe) is to (again) retain OrderID and let the last n characters of order number be the difference between the first OrderID of the day and this OrderID.

All of this may be done from Access' query builder without needing to go to SQL.

Tony Hopkinson's answer was correct but I worried it might be a little terse for someone who's "not an experienced database designer"

Related Discussions

Related Forums