Generate a unique "Number" from a composite of selected fields

[Jason M]

I am trying to generate a unique "number" for a workorder system that I am
creating and seem to be running into a brick wall... Here is the essence of
what I would like to do:

A user would select the departmetn that the workorder is for and this would
become the prefix for the workorder (IE "ABC" or "DEF")

The next two digits would be the month the workorder was generated (IE 10
for october)

The next two would be the two digit year (IE 03)

and the last three digits would be a sequential number that would be reset
each year that would range from 0000 to 9999 giving us 10,000 possible work
orders for each department.

The finished work order would look like this: "ABC-10-03-0001"

I can create the individual pieces easy enough, but I would like to combine
them and use this for the WONumber that will be the identifier for that
workorder in the database (stored without dashes).

If someone could be kind enough to point me in the right direction I would
be appreciative.

TIA, Jason M Canady

 

[John Vinson]

I can create the individual pieces easy enough, but I would like to combine
them and use this for the WONumber that will be the identifier for that
workorder in the database (stored without dashes).

This is called (uncomplimentarily) an "Intelligent Key", and it's
generally considered to be A Bad Idea. You're storing data
redundantly; it's hard to manage; and it isn't even all that helpful
to users. If you want to store a date or a department, by all means do
so - but store it AS DATA, not as part of a redundant artificial key!

That said... if you have the component fields (Department, Orderdate,
Seqno), you can open the table in design view and ctrl-click all three
of them; then click the Key icon. This will make the three fields a
joint Primary Key.

You can then create a Query concatenating the values for display
purposes:

WONumber: [Department=] & Format([OrderDate], "-mm-yy-") &
Format([Seqno], "0000")

 

[Graham Mandeno]

Hi Jason

I would store these as three separate fields. You probably have two of them
anyway - DeptCode and WODate. Just add a third for SeqNum (integer). In
the BeforeUpdate event of your data entry form, you can find the previous
sequence number for the given year and department and increment it:

Me.SeqNum = Nz(DMax("SeqNum", "tblWorkOrders", _
"DeptCode='" & Me.DeptCode & "' and Year(WODate)=" _
& Year(Me.WODate)),0) + 1

You don't need to *store* the assembled work order number - just assemble it
on the fly in a query expression whenever you want to display it or print
it:

WorkOrderNum: [DeptCode] & Format([WODate], "mmyy")
& Format([SeqNum], "0000")

Now, your table should have a primary key (especially if it's on the "one"
side of a relationship with other tables) so for this use an Autonumber
which the user never gets to see.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

 

[Jason M Canady]

Thanks for the input. I was trying not to combine them, but was also trying
to create a unique primary key for my workorders and though that this was
the best approach. Your solution seems to fix that problem.

Thanks again,
Jason

I can create the individual pieces easy enough, but I would like to combine
them and use this for the WONumber that will be the identifier for that
workorder in the database (stored without dashes).

This is called (uncomplimentarily) an "Intelligent Key", and it's
generally considered to be A Bad Idea. You're storing data
redundantly; it's hard to manage; and it isn't even all that helpful
to users. If you want to store a date or a department, by all means do
so - but store it AS DATA, not as part of a redundant artificial key!

That said... if you have the component fields (Department, Orderdate,
Seqno), you can open the table in design view and ctrl-click all three
of them; then click the Key icon. This will make the three fields a
joint Primary Key.

You can then create a Query concatenating the values for display
purposes:

WONumber: [Department=] & Format([OrderDate], "-mm-yy-") &
Format([Seqno], "0000")

 

[Jason M Canady]

Thanks! I like this idea as well as the post above... (did you happen to
read it) . Yours seems to keep with my original intent, and I will see what
I can do to make it work as well.

I am still new to access, and am learning daily.

Thanks again.

Jason

Hi Jason

I would store these as three separate fields. You probably have two of them
anyway - DeptCode and WODate. Just add a third for SeqNum (integer). In
the BeforeUpdate event of your data entry form, you can find the previous
sequence number for the given year and department and increment it:

Me.SeqNum = Nz(DMax("SeqNum", "tblWorkOrders", _
"DeptCode='" & Me.DeptCode & "' and Year(WODate)=" _
& Year(Me.WODate)),0) + 1

You don't need to *store* the assembled work order number - just assemble it
on the fly in a query expression whenever you want to display it or print
it:

WorkOrderNum: [DeptCode] & Format([WODate], "mmyy")
& Format([SeqNum], "0000")

Now, your table should have a primary key (especially if it's on the "one"
side of a relationship with other tables) so for this use an Autonumber
which the user never gets to see.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

I am trying to generate a unique "number" for a workorder system that I am
creating and seem to be running into a brick wall... Here is the

essence

of
what I would like to do:

A user would select the departmetn that the workorder is for and this would
become the prefix for the workorder (IE "ABC" or "DEF")

The next two digits would be the month the workorder was generated (IE 10
for october)

The next two would be the two digit year (IE 03)

and the last three digits would be a sequential number that would be reset
each year that would range from 0000 to 9999 giving us 10,000 possible work
orders for each department.

The finished work order would look like this: "ABC-10-03-0001"

I can create the individual pieces easy enough, but I would like to combine
them and use this for the WONumber that will be the identifier for that
workorder in the database (stored without dashes).

If someone could be kind enough to point me in the right direction I would
be appreciative.

TIA, Jason M Canady