How to generate a very specific primary key based and date and numberfor workorders db

[Peter55]

Hi everyone,
I am very new to Access and I am start off with a work-order tracking data base for my business.
We currently enter our orders as they come in an Excel spread sheet manually.


We want to move on to a database system and have chosen to use access .
We would like to keep our old method of work order numbering.
i.e 1604-001 would be our first order logged for April 2016, next order would be 1604-002 etc, start of next month would be 1605-001
It makes it very easy to back track historical orders.
I think i can set up variables for Date and month and add a starting number of 001
But i am not sure how to index the number or how to automate the change to the date/month variables as the months go by.
Any help in what would be the most logical way to implement this would be appreciated.
Cheers,
Peter

[ranman256]

You can still do your way, but you don't have to. Either way, use an autonumber field.
Youd still have the date of the item, and the autonumber can count the # items in said month. This way its totally passive with no programming.
but,
if you want to use your way, also use your special orderID method, you must program.
on the form load event, (to add new records)
the Autonum field will be null, so,
you must count the # records for that month, then add 1, and create your new OrderID
something like...

vMo = format(date(),"yyyymm"
newNUm = dcount("*","table","[OrderMo]=#" & vMO) & "#)
NewNum = NewNum + 1
OrderID = vMo & NewNum

but not really needed with autonum.

[JoeM]

Welcome to the Board!

So how are new records being entered into the database?
If they are being entered manually, you will need to control all new data entry through a Form (do not have people enter directly into the table). You can then add VBA code on the Form that will calculate this value "on-the-fly" as the data is being entered. I just recently showed someone how to do something like that here: http://www.mrexcel.com/forum/microso...ml#post4476626

If you are importing the data, you will probably first need to import it into a temporary table where this field is not a required field (since you will not be able to populate this primary key with the import), and loop through the recordset to populate this field, then write the data from the temporary table to the permanent table.

The VBA code for calculating the values is similar in both. Basically, you are finding the last entry made for that month, and then adding one to the counter and going from there.

[shadowsedge]

Primary keys are used to differentiate one record from another record in a database.
It should not be used for any other reason (but sometimes is).
This may cause trouble down the line but that is your decision.

I would suggest you create a 2nd entry for your database table and use that for your order tracking.

Here is a good place to start: http://www.functionx.com/access/ (since you said you are "very new")

[John_G]

You might want to consider splitting the order "number" into two parts (i.e. two fields) - the first for the year/month, and the second for the sequence number. You can always display the number as 1604-002 on forms and reports when you need it. Splitting it like that will make working with the sequence numbers quite a bit easier.

Be aware though that this will make searches based on a date range tricky, so if you indend to do that sort of thing, you might want to keep the actual order date as well, in a date/time field. (Yes, I know it's redundant, but only sort of).

If I were doing it, I'd make the year/month field a text field, and calculate it with an expression something like this:

format(date(),"yy") & format(date(),"mm") I have used the current date in this example, but you could use any date (order date, maybe?)

Let's assume you have put this into a VBA variable called YM, like this:

YM = format(date(),"yy") & format(date(),"mm")

Once you have that value, then you can get the next sequence number something like this:

NextSequence = nz(dmax("Seq", "Orders", "YearMonth = '" & YM & "'"), 1)

It might not be obvious, but that will automatically restart the sequencing at 1 each time the year/month changes.

Just some ideas to get you started - post back if you need more assistance.