Want to keep deleted records in a separate table. How?

[Scott Kinney]

I have an inventory database. I want to delete out-of-stock items
from the main database, but keep them in a separate table so
that I can reference data about them.

I created a copy of the item table, structure only, no data.
Then I query the item table for the ones I want, and attempt
to copy them to the out of stock item table. The 'paste' fails
all the time. On the theory that the ItemId (Autonumber in the main
table) is the culprit, I've tried to paste with the itemid in the
out of stock table defined as autonumber, number and text, failing
each time.

Can anyone point me in the right direction? (I just know I'm
missing something obvious.)

Scott

[Larry Linson]

Make the corresponding field in the archive table a Long Integer, not an
AutoNumber field. Use two Queries: one an Append Query to select all the
out-of-stock records in your main table and append them to the archive
table; then another (make sure you don't run it until after you manually
verify that the append did work) that deletes those out of stock records
from the main table.

But, until you are looking at records in the high hundreds of thousands or
millions, Access can handle the situation very nicely. Thus, you might
consider just leaving the records there (obviously "out of stock" is
something you can determine; perhaps you'd want to add a separate
"discontinu ed" indicator) and using Queries to access only the ones that are
in stock and not discontinued, instead of using a separate archival table.

Certainly, any business decision processing you may do that requires both
active and archived data will be simpler if you have it all in the same
table.

Larry Linson
Microsoft Access MVP

"Scott Kinney" <sa******@ix.ne tcom.com> wrote in message
news:iI******** ************@co mcast.com...

I have an inventory database. I want to delete out-of-stock items
from the main database, but keep them in a separate table so
that I can reference data about them.

I created a copy of the item table, structure only, no data.
Then I query the item table for the ones I want, and attempt
to copy them to the out of stock item table. The 'paste' fails
all the time. On the theory that the ItemId (Autonumber in the main
table) is the culprit, I've tried to paste with the itemid in the
out of stock table defined as autonumber, number and text, failing
each time.

Can anyone point me in the right direction? (I just know I'm
missing something obvious.)

Scott

[Scott Kinney]

Larry,

Thank you. I wrestled with just adding an instock/out of stock
indicator. It's lazy of me, but moving the out of stock
items to a separate table seems easier than adding a
new criteria to the fairly large number of queries I have.

Scott
"Larry Linson" <bo*****@localh ost.not> wrote in message
news:jM******** ***********@nwr ddc02.gnilink.n et...

Make the corresponding field in the archive table a Long Integer, not an
AutoNumber field. Use two Queries: one an Append Query to select all the
out-of-stock records in your main table and append them to the archive
table; then another (make sure you don't run it until after you manually
verify that the append did work) that deletes those out of stock records
from the main table.

But, until you are looking at records in the high hundreds of thousands or
millions, Access can handle the situation very nicely. Thus, you might
consider just leaving the records there (obviously "out of stock" is
something you can determine; perhaps you'd want to add a separate
"discontinu ed" indicator) and using Queries to access only the ones that are in stock and not discontinued, instead of using a separate archival table.

Certainly, any business decision processing you may do that requires both
active and archived data will be simpler if you have it all in the same
table.

Larry Linson
Microsoft Access MVP

"Scott Kinney" <sa******@ix.ne tcom.com> wrote in message
news:iI******** ************@co mcast.com...

I have an inventory database. I want to delete out-of-stock items
from the main database, but keep them in a separate table so
that I can reference data about them.

I created a copy of the item table, structure only, no data.
Then I query the item table for the ones I want, and attempt
to copy them to the out of stock item table. The 'paste' fails
all the time. On the theory that the ItemId (Autonumber in the main
table) is the culprit, I've tried to paste with the itemid in the
out of stock table defined as autonumber, number and text, failing
each time.

Can anyone point me in the right direction? (I just know I'm
missing something obvious.)

Scott

[Jeffrey R. Bailey]

I have an argument in favor of leaving the records in the main table. If
you put the out of stock records in a separate table and the main table has
an autonumber field for it's primary key it is possible for an id number
that is in the out of stock table to be used again in the main table for a
different item. I ran across this exact situation with a main inventory
table and an out of stock table (they weren't called that but the effective
system was the same) that only totaled about 80,000 records. Trust me, I
thought as large as a long integer is that this was very unlikely, but it
did happen. Admittedly, it was only a handful of records, but they created
problems for reporting. I was attempting to build a report to show the
current retail market value of the inventory at the time it happened.

This is a pretty good example of what Larry meant in the last paragraph of
his answer.

--
Jeffrey R. Bailey
"Larry Linson" <bo*****@localh ost.not> wrote in message
news:jM******** ***********@nwr ddc02.gnilink.n et...

Make the corresponding field in the archive table a Long Integer, not an
AutoNumber field. Use two Queries: one an Append Query to select all the
out-of-stock records in your main table and append them to the archive
table; then another (make sure you don't run it until after you manually
verify that the append did work) that deletes those out of stock records
from the main table.

But, until you are looking at records in the high hundreds of thousands or
millions, Access can handle the situation very nicely. Thus, you might
consider just leaving the records there (obviously "out of stock" is
something you can determine; perhaps you'd want to add a separate
"discontinu ed" indicator) and using Queries to access only the ones that are in stock and not discontinued, instead of using a separate archival table.

Certainly, any business decision processing you may do that requires both
active and archived data will be simpler if you have it all in the same
table.

Larry Linson
Microsoft Access MVP

"Scott Kinney" <sa******@ix.ne tcom.com> wrote in message
news:iI******** ************@co mcast.com...

I have an inventory database. I want to delete out-of-stock items
from the main database, but keep them in a separate table so
that I can reference data about them.

I created a copy of the item table, structure only, no data.
Then I query the item table for the ones I want, and attempt
to copy them to the out of stock item table. The 'paste' fails
all the time. On the theory that the ItemId (Autonumber in the main
table) is the culprit, I've tried to paste with the itemid in the
out of stock table defined as autonumber, number and text, failing
each time.

Can anyone point me in the right direction? (I just know I'm
missing something obvious.)

Scott

[Larry Linson]

Thanks for the real-life example, Jeffrey.

I was thinking of queries that needed both the "live" and "archived"
inventory information, in which case, you'd have to use a UNION or UNION ALL
query if the information is stored in two tables.

BTW, MVP Allen Browne's site http://allenbrowne.com/tips.html has some
excellent discussion of Inventory applications.

Larry Linson
Microsoft Access MVP