[AccessD] AutoNumbers in historical tables

John Colby jcolby at colbyconsulting.com
Mon Jun 30 09:54:13 CDT 2003


Remember though that the autonumber will continue from the last autonumber
copied into the table, so if you do a restore you MUST go get the last
number, add 1 to it, and insert a fake record with that number, then delete
that fake record back out.  At least that is how I handle it.

John W. Colby
www.colbyconsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Henry Simpson
Sent: Monday, June 30, 2003 10:35 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] AutoNumbers in historical tables


You can set an autonumber field to any valid numeric value that doesn't
pre-exist regardless of what the next number would otherwise be.  Multiple
conflicting numbers would have to be preserved in separate tables or it
wouldn't be an accurate history.  If you wanted to have a master
reconcilliation, that would no longer be historical and there would be no
point in keeping a unque index on the autonumber field.

Hen

>From: "Susan Harkins" <harkins at iglou.com>
>Reply-To: Access Developers discussion and problem
>solving<accessd at databaseadvisors.com>
>To: "Access Developers discussion and problem
>solving"<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] AutoNumbers in historical tables
>Date: Mon, 30 Jun 2003 09:59:06 -0400
>
>You present an interesting situation. Supposing you were required to
>preserve the Autonumber -- how would you go about appending records as you
>archive -- you couldn't I'm guessing -- would you archive to new tables
>each
>time? What a mess. In addition, if you were trying to archive records from
>similar but different databases and you had conflicting AutoNumbers, what
>would you do? In the latter case, I'd say you could simply add a new
>AutoNumber to the historical table and work from that value instead of the
>original, but still, sounds like it could lead to a lot of confusion and
>maybe problems. I hadn't really considered either possibility.
>
>Susan H.
>
>
> > Martin,
> >
> >   The reasons are varied, but in general, your trying to take a snapshot
>of
> > the way the data existed.  This includes not only the entities
>themselves,
> > but the relationships as well.  From a very purist point of view, that
>means
> > not modifying the data in any way.  For example; capturing an image of a
> > hard disk for a criminal investigation.  You must make a copy that is
>bit
> > for bit identical to the original.  The process of copying cannot have
>the
> > potential to introduce errors or it will get tossed in court.  Doesn't
> > matter if the process did or didn't introduce errors, just that the
> > possibility exists or not.  IP tunneling is another example where the
> > original packet of data cannot be touched, so it gets encapsulated.
>It's
>a
> > very general concept that is applied in many situations.
> >
> >   Then there are all the reasons why most relational systems today have
> > gravitated towards the use of surrogate keys; isolation from the data,
> > performance, decreased storage requirements, etc.  These get to be
>critical
> > when talking about large data marts/warehouses in the Tera/Peta byte
>range.
> >
> >   Isolation from the data is an important one as the data mart may span
>a
> > considerable time period.  Some things that might happen:
> >
> > 1. Production DB keys might possibly be reused through normal
>operations.
> > 2. Production DB may re-use keys even though it is not supposed to.
> > 3. An entities attributes may change through time, but it's key may not.
> > 4. Key formats in a production system may change.
> > 5. Acquisition of another company may yield a situation where key
>sequences
> > conflict or keys are of a different type.
> >
> >
> >   Some of these things are only valid if the existing system uses
>natural
>or
> > surrogate keys.  Some apply to both.  As I said the reasons are varied.
> >
> >
> > Jim Dettman
> > President,
> > Online Computer Services of WNY, Inc.
> > (315) 699-3443
> > jimdettman at earthlink.net
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
> > Mwp.Reid at Queens-Belfast.AC.UK
> > Sent: Sunday, June 29, 2003 3:40 PM
> > To: Access Developers discussion and problem solving
> > Subject: RE: [AccessD] AutoNumbers in historical tables
> >
> >
> > Just out of interest why would you want to?
> >
> > Martin
> >
> > Quoting Jim Dettman <jimdettman at earthlink.net>:
> >
> > > Susan,
> > >
> > >   Depends on how much tractability you need/want.  Typically in a data
> > > mart,
> > > you don't touch any of the original data including the keys, meaning
> > > that
> > > you add a new key structure outside of any existing.
> > >
> > >   Also depends a lot on how your DB is structured: natural or
> > > surrogate.
> > >
> > > Jim Dettman
> > > President,
> > > Online Computer Services of WNY, Inc.
> > > (315) 699-3443
> > > jimdettman at earthlink.net
> > >
> > > -----Original Message-----
> > > From: accessd-bounces at databaseadvisors.com
> > > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Susan
> > > Harkins
> > > Sent: Saturday, June 28, 2003 5:27 PM
> > > To: AccessD at databaseadvisors.com
> > > Subject: [AccessD] AutoNumbers in historical tables
> > >
> > >
> > > When archiving historical data do you change an AutoNumber field to a
> > > Number
> > > field? Like to hear pros and cons on the subject.
> > >
> > > Susan H.
> > >
> > >
> > > _______________________________________________
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> > >
> > > _______________________________________________
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> > _______________________________________________
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> >
> > _______________________________________________
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> >
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
Add photos to your e-mail with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=features/featuredemail

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





More information about the AccessD mailing list