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
"discontinued" 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.netcom.com> wrote in message
news:iI********************@comcast.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
