Auto Numbering

opostal

Registered User.
Local time
Yesterday, 20:15
Joined
Jun 26, 2009
Messages
47
I am relatively new to Access but have some minor ability with it and would like to get some assistance regarding numbering. The purpose of my database is to control project revisions. For example let us say you have dozens of technical volumes (projects) with more being added each month. For each volume, revisions come out fairly regularly. I want to track each revision by project and I want the authors of the revision to be able to use a simple form to add the relevant information of the revision. Additionally, I would like the revision number to automatically generate a sequential number per project ensuring the same number is not used twice or a sequential revision number skipped. If I use auto numbering I can achieve that goal for each individual project but would have to create a database for each project to accomplish this. It would be very cumbersome to have to create an individual database for each project, however if I do not do that, I can see no way to accomplish this goal. Please let me know of any ideas you may have to simplify this process. Thank you very much for any assistance.
 
Welcome to the site. You certainly don't want individual databases. This type of question comes up all the time. If you search on DMax, a common method of finding the highest previous value, you should find several threads.
 
That looks like the very item I am looking for. Thank you for the quick response and your time.
 
No problem; post back if you get stuck.
 
Ok, let me add one more variable to this list. The DMax feature seems to be what I want, however, I noticed that some of the revisions have changes on them listed alphabetically. Therefore I get a total amount of data listed as Project (by number) Revision (also numerical) and finally some revisions have changes (alphabetical, a-e being the largest quantity I have seen at this point). As near as I can figure it out, the DMax function will allow me to serch for and label the highest value meeting a criteria for Project in the revision area, but will not account for any changes. I can use the VALUE command to assign values to the letters in the changes which should then allow me to display what the highest revision and change applied was. My question would be whether or not I could do a search matching the project number, use DMax as a function to ascertain the highest revision, sort with the change and display this value? Hope that is reasonably clear as it begins to get muddy for me and I am authoring it...lol. If there is a simpler way to do this please let me know as I am no genius at this point. In summary I would like to have a dbase with three main variables Project (a number), Revision to the project number (also a number associated with various projects) and lastly a change to the revision (assigned by a letter at the moment). If possible when filling out a form for this data I would like to have the next available sequential revision number automatically displayed but if that is too complicated, I am ok with a quick look at existing revisions/changes to allow the user to assign the next number. I am making progress with the advice provided and thank you for your time but thought maybe I would try to readress this to see if there is an easier solution. Thanks again for your time.
 
DMAx can do that, too, but the criteria segment gets more complex.

As your identifier gets more complex, you need to consider the issue of key sizing.

You can make a key that looks like nnnnnn-vvvvv-c where your nnnnnn is a technical volume identifier, vvvvv is the revision/version, and c is your secondary rev letter. I would approach that as having a three-part compound primary key. (Access Help covers the topic of compound keys.) It is, however, also possible to take a different approach that might or might not help. Depends on whether this confuses or enlightens you.

Presumably, there is something about a tech document that you need to track that might require it to have data in a child table. (Otherwise, this is just a complex spreadsheet app.) Some folks would say that as your keys get more complex, you might wish to create an autonumber Prime Key for that table and then place indexes on the three fields that you want to track as identification. (Non-unique indexes.) Then you can narrow down the records you want with single-key searches to find all revisions of a given technical document; or all updates of revision x of document y; or specifically, update z of revision x of document y, depending on how you frame the query.

Finding the DMax of update or revision or document then becomes a matter of what you put in the criteria segment of the DMax.

Either way, to find the highest update, you would need to find the DMAX( "[updateletter]","documentlist","[RevisionNumber]="&CStr(x)&" and [TechDocNumber]="&CStr(y))

What you do with that is of course your call.
 
Thank you for the quick response. I will continue working with that data as that sounds like what I need. Thanks again for your time and I will post again should I hit another wall, but I feel comfortable I have enough information to be successful.
 

Users who are viewing this thread

Back
Top Bottom