Tags: access, database, delete, deleted, inventory, itemsfrom, microsoft, mysql, oracle, out-of-stock, records, reference, separate, sothat, sql, table

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

On Database » Microsoft Access

9,005 words with 4 Comments; publish: Mon, 31 Dec 2007 00:04:00 GMT; (250125.00, « »)

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

All Comments

Leave a comment...

  • 4 Comments
    • 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" <sakinney.ms-access.todaysummary.com.ix.netcom.com> wrote in message

      news:iI6dnTX0UKqtmZHdRVn-hA.ms-access.todaysummary.com.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

      #1; Mon, 31 Dec 2007 00:05:00 GMT
    • 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" <bouncer.ms-access.todaysummary.com.localhost.not> wrote in message

      news:jMTOb.20275$9U6.18748.ms-access.todaysummary.com.nwrddc02.gnilink.net...

      > 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" <sakinney.ms-access.todaysummary.com.ix.netcom.com> wrote in message

      > news:iI6dnTX0UKqtmZHdRVn-hA.ms-access.todaysummary.com.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

      #2; Mon, 31 Dec 2007 00:06:00 GMT
    • 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" <bouncer.ms-access.todaysummary.com.localhost.not> wrote in message

      news:jMTOb.20275$9U6.18748.ms-access.todaysummary.com.nwrddc02.gnilink.net...

      > 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" <sakinney.ms-access.todaysummary.com.ix.netcom.com> wrote in message

      > news:iI6dnTX0UKqtmZHdRVn-hA.ms-access.todaysummary.com.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

      #3; Mon, 31 Dec 2007 00:07:00 GMT
    • 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

      #4; Mon, 31 Dec 2007 00:08:00 GMT