Post by AppuHi I am new to access and Need help on the following
I need to create a work order number automatically (the format is 001) and it is by the product ID. Eg. select the Product ID and get the next work order number for that product, if there is no work order number for that product, the work order number should be 001. One Product can have many work order.. one work order can have only only Product ID. Much appricited for your help. thanks
That would be a User Friendly number. don't use it as your primary key. you can create a field that you store with the zeroes in front as a text field or you can simply store a number and pad it to show zeroes in front.
a primary key should be an auto number field by default. it will however not remain sequential. when a user starts creating a record and then backs out of it, they will have "used up" a number in the sequence. if it is important to have numbers in sequence then you will need to create an auto number system that relies on the previous records to find the last number used.
the primary key / auto number fields that Access has are used to uniquely identify each record in each table. they are used to build relationships between your data tables and show how the Product ID relates to a Work Order ID.
in your scenario I see that one product ID will have many work orders ids. that is called a 1 to many relationship. you will need at least 2 tables.
your Product table could look like this:
ProductKeyID Autonumber used to uniquely identify the records
ProductID Text Data Type used to store the "user friendly" number
PDescription Text Data Type used to describe the product
PCreateDate Date/Time Type make this field default data to =now()
PObsoleteDate Date/Time Type have a button on form to "date stamp" "deleted"
OtherFields Various Types you will most likely need other fields
your WorkOrder table could look like this:
WorkOrderKeyID Autonumber used to uniquely identify the records
WorkOrderID Text Data Type used to store the "user friendly" number
ProductKeyID Number Type used to store the ProductKeyID it relates to
WODescription Text Data Type used to describe the work order
WOCreationDate Date/Time Type make this field default data to =now()
WOCompletedDate Date/Time Type have a button on form to "date stamp" completion
OtherFields Various Types you will most likely need other fields
you will almost likely need other tables to store the information about each of the materials and operations performed on the work orders.
the "user friendly" fields take a little bit of code and queries to set up as you need to determine that a new record is being created, find the last number in that field and then make a new number and dump it in the field. it is doable and I have done it, even though I don't like it. in my personal preference I would rather just show the ProductKeyID and WorkOrderKeyID and not have the extra work of the user friendly numbers.
I have done this kind of database creation for the last 10 years in a manufacturing company and have been forced to work around user wishes, program limitations and manufacturing needs. I could post some examples or look at what your cooking and help out a bit.