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

  • Permalink
  • submit to reddit
  • Email
  • Follow


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


0
Reply sakinney (80) 1/19/2004 4:22:07 PM

See related articles to this posting


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@ix.netcom.com> wrote in message
news:iI6dnTX0UKqtmZHdRVn-hA@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
>
>


0
Reply bouncer (4168) 1/19/2004 4:40:47 PM

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@localhost.not> wrote in message
news:jMTOb.20275$9U6.18748@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@ix.netcom.com> wrote in message
> news:iI6dnTX0UKqtmZHdRVn-hA@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
> >
> >
>
>


0
Reply sakinney (80) 1/19/2004 5:14:44 PM

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@localhost.not> wrote in message
news:jMTOb.20275$9U6.18748@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@ix.netcom.com> wrote in message
> news:iI6dnTX0UKqtmZHdRVn-hA@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
> >
> >
>
>
>


0
Reply MrWizard2903RemoveMe (18) 1/19/2004 8:35:33 PM

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




0
Reply bouncer (4168) 1/19/2004 10:09:14 PM
comp.databases.ms-access 42285 articles. 10 followers. Post

4 Replies
102 Views

Similar Articles

[PageSpeed] 37


  • Permalink
  • submit to reddit
  • Email
  • Follow


Reply:

Similar Artilces:

"Specify Table containing the Records you want to Delete" Problem
Hi: Access 2000 W98! I have a table with numerous records in it, and am attempting to delete certain records that have been selected from it. These are selected based on the ID number in a different table. While I am using the tools in Access for query setup, its easier to show it on here using the SQL for the query, which is as follows( the table is [Investment Action Units]): DELETE [Investment Action Units].date, [Investment Action Units].[Action name], [Investment Action Units].Units, [Investment Action Units].Value, [Investment Action Units].Remarks FROM [Investment Action Units] INNER...

Delete Records From a Table Using Records in Another Table.
I have been searching many postings and I cant seem to find anyone that has this answer so I decided to post. I am using SQL (Transact-SQL).If I have 2 tables with columns acct_num,activity_date,and pay_amt and I want to delete one instance of a record in table 1 for every instance of that record in table 2 how could I do that. For example. Table 1 ----------- acct activity_date pay_amt 123 5/1/2004 50.00 123 5/1/2004 50.00 123 5/1/2004 50.00 123 5/1/2004 50.00 123 5/1/2004 50.00 Table 2 ----------- acct activity_date pay_amt 123...

Deleting records in one table having a matching related record in another table?
DELETE tblPreliminaryVINs.* FROM tblPreliminaryVINs INNER JOIN tblVehicleJobs ON tblPreliminaryVINs.PVIN = tblVehicleJobs.SerialNum; The above SQL does not work for me. I get an error I cannot delete the record(s) because of READONLY and PERMISSIONS related issues. I can hilite (select) the record in the table and delete it. I can use the following SQL to delete record(s) in tblPreliminaryVINs DELETE tblPreliminaryVINs.* FROM tblPreliminaryVINs; But I really only want to delete records in tblPreliminaryVINs whose [PVIN] field has a matching value somewhere in blVehicleJobs's [SerialNum] ...

Delete one instance of a record in a table for each instance of that record in a 2nd table.SQL
I have been searching many postings and I cant seem to find anyone that has this answer so I decided to post. I am using SQL (Transact-SQL).If I have 2 tables with columns acct_num,activity_date,and pay_amt and I want to delete one instance of a record in table 1 for every instance of that record in table 2 how could I do that. For example. Table 1 ----------- acct activity_date pay_amt 123 5/1/2004 50.00 123 5/1/2004 50.00 123 5/1/2004 50.00 123 5/1/2004 50.00 123 5/1/2004 50.00 Table 2 ----------- acct activity_d...

forbit deleting a record if it has related records in an other table
How can I prevent the deletion of a record if it has related records in FM8.5 pro? In FM6.0 I used the isvalid(othertable::field) function to determin the existance of related records. This doesnt seam to work in 8.5. Any sugestions? In article <erd2k0$ljl$1@mouse.otenet.gr>, "Hatzigiannakis Nikos" <ypai@aigaio.gr> wrote: > How can I prevent the deletion of a record if it has related records in > FM8.5 pro? > > In FM6.0 I used the isvalid(othertable::field) function to determin the > existance of related records. This doesnt seam to work in 8.5....

Delete Query-Identifying the table with records for deletion
HI again: I have a query which selects records on tblPOmods on the basis of information on two other tables. I wish to delete the selected transactions, and am having a problem doing it. It tells me that if "could not delete from the specified tables". I have tried to set the "where" to "From" for the fields I selected, but the "From" wont take and bounces back to "where". I have tried other strategies, and got a comment "Identity table to delete", but the "where" still won't change to a "From". Does anyone ...

Delete record based on existence of another record in same table?
Hi All, I have a table in SQL Server 2000 that contains several million member ids. Some of these member ids are duplicated in the table, and each record is tagged with a 1 or a 2 in [recsrc] to indicate where they came from. I want to remove all member ids records from the table that have a recsrc of 1 where the same member id also exists in the table with a recsrc of 2. So, if the member id has a recsrc of 1, and no other record exists in the table with the same member id and a recsrc of 2, I want it left untouched. So, in a theortetical dataset of member id and recsrc: 0001, 1 0002, 2 ...

Deleting records in multiple tables (and updating another table)?
Hi, I'm attempting to create a web form to allow an administrative user to "clean up" the records of a (former) user, but I am uncertain about how to phrase my query in SQL. The web form (the back end of which will be speaking to the database via PHP) knows the id of the former user, and needs to carry out the following actions when the form is submitted: The user may have zero or more records in each of the following tables, which can be found as the person_id field in such records will match the user id: researcher publication publication_source qualification profile_rec...

delete five BIG tables and insert new records on same tables
I need to delete five BIG tables and insert new records on same tables. Since it involved lot of computing, it will take long. I'm thinking it will take probably couple of days. What I want is minimal down time, if I have to have little down time. I have sql insert scripts to run. Any suggestions? Thanks. -Jay PC wrote: > I need to delete five BIG tables and insert new records on same tables. > Since it involved lot of computing, it will take long. I'm thinking it > will take probably couple of days. > > What I want is minimal down time, if I have to have lit...

Simple code to delete all rcd in a table and import records to same table
Hi, I am trying to create a form that would have only two buttons on it. When the user clicks on one of the buttons it will issue a prompt such as Do you wish to delete all records from the Classes Table? YES NO Upon clicking on yes, then all the records in the classes table would be deleted. The second button when click would ask: Do you wish to import records to the classes table. If the user clicks the yes button then records are imported from a ascii(Text file, with field delimiters) into the classes table. Know this should be simple, but I seemed to be maki...

Trigger to delete record from one table with check on another table.
Hi. I want to create a trigger that only allows delete from table A if corresponding record in table B does not exist. Any idea on how this can be done? Thanks, Kenneth. Is there a column in table B that references the primary key of table A? If so, won't the constraint prevent the deletes you want to prevent? Don't you wanna use referential integrity constraints to achieve this? kenneth.osenbroch@telenor.com (Kenneth Osenbroch) wrote in message news:<ce7df6a2.0405260105.55c4489@posting.google.com>... > Hi. I want to create a trigger that only allows delete f...

Keeping the focus on the next record when deleting a record in a subform (Access 2003)
I have a list of records in a subform that a user can either edit or delete. This is an unbound form. If the user deletes a record, I want to refresh the form, and then position the cursor on the next record on that subform. This seems like that this should be a fairly easy thing to accomplish, but I cannot get it to reposition the cursor on the next record. The following is my code segment: Any help would be greatly appreciated!!! Thanks. Stuart ---------------------------------------------- (The record source is a query that returns all the records from the Card1 table) Set rst = ...

How to indicate which table's records get deleted in a 2-table query.
I had a table (tblReturnReceipts) with 47 records and I wanted 18 of its records deleted. So I thought I would be smart and make another table (a temporary junk table) named BadRRIDs and use it in the following Select query... SELECT tblReturnReceipts.RtnRcptID, tblReturnReceipts.CorrespID, tblReturnReceipts.DateSigned, tblReturnReceipts.OLA, tblReturnReceipts.OLA_ID, tblReturnReceipts.RRTDstamp, tblReturnReceipts.RRuser, tblReturnReceipts.Tracked FROM BadRRIDs INNER JOIN tblReturnReceipts ON BadRRIDs.RRID = tblReturnReceipts.RtnRcptID; I wanted to run the query, which lists all the records ...

Query filter - Joined Tables
Hello, I have a Database setup that has two tables that are linked. Table1 contains a list of people with their basic contact details (name & phone number etc.) and Table2 contains a list of all marketing calls made to the people on Table1. The tables are linked via an ID # field. What I would like to create is a query that shows all contacts on Table1 who we have not made a call to. The problem I'm experiencing is that as these people have never been called, they don't have any records (calls) on Table2. I'm struggling to find a filter that will filter out people who have re...

Deleting a record from a table.
Hi, I am writing code for a project I am working on using Accewss 2000, and when I choose an item from a drop-down box, I want to delete it form the table it is getting the info from. I am using the following code: 'Dim dbGiant As Database 'Dim rsGiant As Recordset 'Set dbMyDB = OpenDatabase("Giants_Sign-UP.mdb") 'Set dbGiant = CurrentDb() 'Set rsGiant = dbGiant.OpenRecordSet("school_all", dbOpenDynaset) 'If Not rsGiant.EOF Then rsGiant.MoveFirst 'Do While Not rsGiant.EOF 'lstRecords.AddItem rsMyRS!Name 'lstRecords.ItemData(l...

deleting a record from table
hey, me again i want to delete a record (product) from a table def list @products = Product.find_all end def show @product = Product.find(@params["id"]) end def delete Product.delete(@params["id"]) end the deleting works but then i get the error htere is no template, but i just want to delete it and then show all products again (list) On Sun, 2005-09-25 at 01:01 +0900, Nick wrote: > hey, me again > > i want to delete a record (product) from a table > > def list > @products = Product.find_all > end > > def show > @p...

Deleting all records in a table
How do I delete all records in a table instead of having to iterate through all the data using Python and deleting by ID? Vernon Wenberg III wrote: > How do I delete all records in a table instead of having to iterate > through all the data using Python and deleting by ID? DELETE FROM table -- //Aho On Dec 21, 10:35 pm, "J.O. Aho" <u...@example.net> wrote: > Vernon Wenberg III wrote: > > How do I delete all records in a table instead of having to iterate > > through all the data using Python and deleting by ID? > > DELETE FROM table > >...

deleting records from table
I wish to tidy up a table of Customers by deleting those records that are not used in other tables. The Customers table has a primary key of CustomerID. A number of other tables have this as a foreign key. Using the Query Designer I have created : DELETE Customers.* FROM (((Customers LEFT JOIN LiftAccounts ON Customers.CustomerID = LiftAccounts.CustomerID) LEFT JOIN LiftServices ON Customers.CustomerID = LiftServices.InvoiceToID) LEFT JOIN StandardComponents ON Customers.CustomerID = StandardComponents.PreferredSupplierID) LEFT JOIN Suppliers ON Customers.CustomerID = Suppliers.SupplierID ...

Deleteing duplicate records from my table
I just discovered that all my records appear twice inside my table, in other words, they repeat on the row below. How can I delete all of the duplicates? I'm sure there must be a tidy line of sql to do that. Thanks, Bill billzimmerman@gospellight.com (Bill) wrote in message news:<8da5f4f4.0307310856.79830a8e@posting.google.com>... > I just discovered that all my records appear twice inside my table, in > other words, they repeat on the row below. How can I delete all of the > duplicates? I'm sure there must be a tidy line of sql to do that. > > Thanks, > &g...

delete all related records from other tables too
I have customer table and it has one to many relation with sales table and sales table has one to many relation with saledetail table. Is it possible if I delete a customer from customer table then all records in sales and seledetail table are deleted too. Custome: CustID, custname, Custadd Sales saleID, CustID, date SaleDetail: saleDetID, SaleID, ProductID,QtySold etc. Can any one help plz what query I should use. khan wrote: > I have customer table and it has one to many relation with sales table > and sales table has one to many relation with saledetail table. Is it > pos...

deleting records that match other table
I am trying to a DELETE that I thought would be simple but isn't. I have table A and table B. Both have a column named "Number". There are duplicate records in the two tables. All I want to do is delete all of the records in table A that exist in table B. In essence where A.number = b.number. I tried: delete from TABLE_A WHERE [A].[NUMBER] = [B].[NUMBER] Got an error. Any help would be great! Thank you, Mark On Wed, 08 Mar 2006 17:40:34 -0800, MSWEENEY wrote: > delete from TABLE_A > WHERE [A].[NUMBER] = [B].[NUMBER] You're aware that "NUMBER" is ...

How to delete all the records in a table in VBA?
Silly question, but I never meet this before. I think a simple delete query will do. thanks Private Sub DeleteAll_Click() Dim StrSQL As String StrSQL = "Delete * from Your_Table;" DoCmd.SetWarnings False DoCmd.RunSQL StrSQL DoCmd.SetWarnings True End Sub "swingingming" <ming.mge@gmail.com> wrote in message news:1123361318.095504.222530@g14g2000cwa.googlegroups.com... I think a simple delete query will do. thanks ...

disable deleting records in table
Dear All, Is it possible to disable deleting records in a table or query ? Filip Yes and No What are you using as the data store? Access Jet/Ace or some SQL database server. If you are using Access native database to store the data and you allow people to work directly with the tables and directly with updateable queries then it is NOT possible to stop records from being deleted. If you control all access to the data using forms then it is possible to cancel any delete actions or preclude them in the first place. Set all forms' AllowDeletions property to False (N...

Retrieving records deleted from table
Please tell me it's possible to retrieve records deleted from an Access table and how to do it. Thanks! OK, It's possible to retrieve records deleted from a table. Happy? Now for the bad news ... it's really not possible. There is no undo available in Access. That is why they put up the messages saying ARE YOU SURE? before allowing you to delete. The only way to get them back would be if you have a backup copy of the database (a nightly tape backup here in my company has saved the day many times!). David ladenlyng@aol.com (denilynn) wrote in message news:<378d05cf.031018182...