3 replies
by wurthjt Accepted solution
by tony hopkinson Accepted solution
Intelligent numbers are a major pain and keying your database on public keys is paving stone one on the road to hell.
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.
by techrepublic Accepted solution
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"
This discussion is closed to new replies.
Trending Software posts
Sort by Trending