Generating an ID

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a pretty simple table that currently has only three columns; one is an
autonumber, one is called "Order Type", and the last is called "Order ID".
What I'd like to do is create an Order ID using the Order Type, Autonumber,
and add in a date as well. What I did was create a query with Order Type and
Autonumber and then created an expression field called "ID". My expression
looks like [ID]: [OrderType] & "-" & Format(Date(),"mmddyy") & "-" &
[autonumber].

What I'd like to do is take that expression and store it in the original
table in the Order ID field. Would an append query fix this? Any help with
this would be appreciated.

Thanks,

Kevin
 
Kevin:

Don't store the result of the expression. It would constitute redundancy
and leave the door open to update anomalies. Keep the autonumber and
OrderType columns and add a column DateTimeStamp with a Default Value
property of Now(). This will enter the date and time when a new row is
inserted into the table. Although you don't need the time of day you may
well find it comes in very useful in the future to have a unique date/time
stamp for each record.

You can then compute the OrderID in a query whenever necessary with your
expression, but substituting the DateTimeStamp column for the Date function.

Ken Sheridan
Stafford, England
 
I have a pretty simple table that currently has only three columns; one is an
autonumber, one is called "Order Type", and the last is called "Order ID".
What I'd like to do is create an Order ID using the Order Type, Autonumber,
and add in a date as well. What I did was create a query with Order Type and
Autonumber and then created an expression field called "ID". My expression
looks like [ID]: [OrderType] & "-" & Format(Date(),"mmddyy") & "-" &
[autonumber].

What I'd like to do is take that expression and store it in the original
table in the Order ID field. Would an append query fix this? Any help with
this would be appreciated.
Click to expand...

Don't.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

Just add an OrderDate field in your table, default value =Date(), and
generate your ID as needed, rather than storing a composite,
non-atomic, redundant field.

John W. Vinson[MVP]
 
Back
Top