Updating recordsets w/o losing original record

  • Thread starter Thread starter John R
  • Start date Start date
J

John R

I am writing some code for a production tracking program
in which the user needs to be able to follow a unit
through production. Each unit is assigned a unique
identifier with the last digit counting the number of
changes made. I want to be able to save *each* operation
in my tables by making a copy of the current entry (w/ all
fields), updating my identifier, and saving everything
(including the most recent changes) to a new record under
the now-updated unique identifier while maintaining all
previous records for that particular unit.

Can anyone offer some advice?

Thank you.
John
 
John,

Please reconsider how you are implementing this. By using
the final character as a sequence number, you violate the
first normal form of database design, and make it more
difficult for yourself, all in one step.

I would add another field (SequenceID) which would be used
in addition to the "UniqueID" field as your primare key.
By doing this, you :

1. Don't limit the number of steps in the production
process (with only one character, you are limited)
2. Make it easier to add a copy of the previous record

This method makes it easy to identify the next sequence
number for a particular "UniqueId" by using the DMAX
function like:

NewSeqID = NZ(DMAX("SequenceID", "yourTable", "UniqueId
= " & txt.UniqueID), 1)

Then, you can create an insert query somewhat like the
following:

INSERT INTO yourTable (UniqueID, SequenceID, field1,
field2, ...)
SELECT UniqueID, NewSeqID, txt.field1, txt.field2, ...

HTH
Dale
 
Dale,

Thanks for the advice. I may not have been completely
clear in my previous post... I have an alpha-numeric
string as the identifier, with the last character being
the increment. I have coded for the program to take that
last character, separate it from the identifier string,
convert the last character to an integer, increase that
integer by one, reconvert to a string and replace the old
increment with the new one.

In any event. I'll definitely take a look at that DMAX
procedure.

Thanks,
John
 
John said:
I am writing some code for a production tracking program
in which the user needs to be able to follow a unit
through production. Each unit is assigned a unique
identifier with the last digit counting the number of
changes made. I want to be able to save *each* operation
in my tables by making a copy of the current entry (w/ all
fields), updating my identifier, and saving everything
(including the most recent changes) to a new record under
the now-updated unique identifier while maintaining all
previous records for that particular unit.
Click to expand...


Adding to what Dale said, not only should you use two fields
for this, but you should use two tables. One for the Units
and another for the Changes. This would eliminate the need
to duplicate the fields in the units table.

If you don't see the reasons why this is the right way to
design your data structure, please consider that you are
using a relational database and that using a spreadsheet
approach is going against the grain, which will cause you
more and more trouble as go further down the road.
 
I am writing some code for a production tracking program
in which the user needs to be able to follow a unit
through production. Each unit is assigned a unique
identifier with the last digit counting the number of
changes made. I want to be able to save *each* operation
in my tables by making a copy of the current entry (w/ all
fields), updating my identifier, and saving everything
(including the most recent changes) to a new record under
the now-updated unique identifier while maintaining all
previous records for that particular unit.

Can anyone offer some advice?
Click to expand...

Yes. DON'T.

Storing derived data, such as a count, is a bad idea in the first
place. Storing two different pieces of information, such as a unique
identifier and a count, in the same field is very risky, and bad
design! Is ID 3312411 the first update to item 331241, or the eleventh
update to 33124? How can you possibly tell!?

I'd suggest instead a one to many relationship between your table and
an Updates table tracking the changes; or at the very least, storing
the Update number in a separate field. Note that Access allows you to
define a Primary Key consisting of up to *ten* fields, so you could
have a Primary Key consisting of your item number and a (separate)
update number.
 
Back
Top