Creating/finding next value (Access/MySQL combo)

[flugh]

I'm working on a database for a friend's growing trucking company. I need to generate a 'manifest' number, thinking the format 'YYMMDD'+UNIQUE (YearYearMonthMonthDayDay+Unique 3 digits) is sensible. I'm wondering what's a good way to get the next manifest number.

* Ok, need to set up a new run on a given date. There are 0 or more manifests already for that date.

* I don't want to just assume anything. But is a 'count' of current records in 'manifest_ids' with date = <what i'm working on> a good idea? I don't think so, since I could delete a manifest in the middle, and the count could make a new manifest number on top of an existing one that was after the deleted one.

* So, take a text (unless there's leading zeroes in integers), say '020804001', and get the next available manifest number ('020804002' maybe, or maybe not ;-)).

I currently have some kludge that puts arbitrary values for each client, and each truck, to make unique values on each date. I need to get away from that though (you can imagine the aggravation as we scale up, keeping track of what's what). Or maybe it's a better way to base it on the client's unique ID?

Sorry if scatter-brained. If you have any suggestions, I'm all ears!

----
JBR

 

[MinusM]

The generation of Text AutoNumbers is a common problem seen in this site

thread705-723396 is one of them

The suggestion is use Dmax to find the last Number. increment by 1 and use
The syntax will look like this
LastIdString= DMax(&quot;manifest_ids&quot;, Tablename&quot;,&quot;manifest_ids Like &quot; & Datewhati'mworkingon & &quot;*&quot;> )
LastId= Right(LastIdString,3)


Best of luck

 

[JeremyNYC]

Dmax is actually usually ruled out in any multi-user systems, as it's far too easy to have collisions and create duplicate IDs. A good system will use a table that stores the next ID, lock that table, run something equivalent to a dmax (usually a recordset) to get the number, increment it, and then unlock the table.

There's code in the Access [version number] Developer's Handbook to do this. But the locking will be different for MySql.

Code that doesn't lock the table really shouldn't be used in a multi-user database.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[flugh]

I apologize for the poorly formed question. It was late, I was tired

Here's what I'm currently doing:
dat_ptruck -- 'pseudo' name for truck (truck 1, south, whatever, there's 1-6 for each client).
dat_ptruck.tripid -- a 2 character text that gets used in the creation of the tripid/manifest number.
dat_ptruck.client -- the client this 'pseudo truck' runs for.

So, I take the date (MMDDYY)
then the client's ID (CC)
then the pseudo truck's tripid value (PP)

I end up with MMYYDDCCPP. Seems to work ok, but as I learn more, I see me database needs more normalization and less kludge (things I did while learning and writing this from scratch).

I'd like to just do a 1 click thing where, if I need 9 trucks total tomorrow, intX=msgbox(&quot;how many trucks for this date?&quot, then insert intX new records into the manifest table. This would also allow me to pre-plan things a bit by putting all stops in a 'pool', then assigning them to trucks (would track the client-to-bill better than the pages of switch/case statements I now have!).

The original method worked, and can still do ok, but as we mix clients among trucks, it's getting a lot more hairy to track billing and payroll. Unhappy paydays need to go away

Any advice is appreciated. Or a good shoulder to cry on. Moving from the basement to a real business is harder than we thought!

----
JBR