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

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
sakinney (80)
1/19/2004 4:22:07 PM
comp.databases.ms-access 42532 articles. 0 followers. Post Follow

4 Replies
144 Views

Similar Articles

[PageSpeed] 35
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
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
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
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
bouncer (4168)
1/19/2004 10:09:14 PM
Reply:
Similar Artilces:

keep copy of an array?
Hello Say I have an array myArray = [1,2,5,4]; and then to get an order of events I use var count = myArray.pop() so the order is 4, then 5, then 2 and finally 1 and of course myArray is then empty. Is there a way to keep a copy of the original myArray? Cheers Geoff Geoff Cox wrote: > Say I have an array > > myArray = [1,2,5,4]; > > and then to get an order of events I use > > var count = myArray.pop() > > so the order is 4, then 5, then 2 and finally 1 and of course myArray > is then empty. > > Is there a way to keep a copy of the original ...

Common problem: "My Ubuntu keeps freezing"
http://ubuntuforums.org/showthread.php?t=1008411 Linux just w... On 2008-12-14, DFS <nospam@dfs_.com> wrote: > http://ubuntuforums.org/showthread.php?t=1008411 > > Linux just w... It's nice to run an OS where the detractors have to find things to complain about via the web. It's not like that other OS where detractors have their own personal travails to complain about. -- ....as if the ability to run Cubase ever made or broke a platform. ||| ...

ListBox.AddItems wants TObject but have Variant
ListBox.AddItems wants TObject but I have Variant's. Seems to force me to create container objects - but where to store them and not loose mixup/memleak ... How do I best handle this. Is it possible to derive a class from 2 base classes in Pascal...? robert "robert" <no-spam@no-spam-no-spam.invalid> wrote in message news:em8qrl$c88$1@news.albasani.net... > ListBox.AddItems wants TObject but I have Variant's. > Seems to force me to create container objects - but where to store > them and not loose mixup/memleak ... > How do I best handle this...

Help Wanted: Ruby Weather Monitoring System
I have started a Ruby based Weather Monitoring System project. Please see http://wxmonitor.sourceforge.net for details. If anyone would like to get involved, please contact me. Billy ...

Trouble saving of record
Hi all, I've constructed a database that handles data concerning the "flow" of tooling in a factory. The problem is in receiving back the concerned tools and storing this data in a table tbl_In. Entry is done via a form that opens already prefilled with the tool number and a txt field that contains the current date/time. When clicking the OK button this data should be stored into the table. However, it doesn't. When I create an extra field in both the table and the form in which a manual input is done the records saves flawless. I've put code on the OK button like: DoCm...

getElementsBName and <table>'s
I relatively new to JavaScript and the DOM.... I have a <table name="xxx"> and I try to a document.getElementsByName("xxx") I don't seem to get the <table> returned in the NodeList returned by getElementsByName If I do a nl = document.getElementsByTagName("table") I can iterate through the resultant NodeList and find node with nl[i].name = "xxx" Do <table>'s just not work with getElementsByName ? Thanks Mike "Mike Berger" <mbergerREMOVETHIS@skypoint.com> wrote in message news:vpdankqdi9l6a5@corp.supernew...

Trimming margins/deleting area (Acrobat V6)
Hello, I want to delete a certain area of ALL pages of an pdf-file. Unfortunately with trimming the margins that doesn't work, because the content is only hidden and so still existing. Is there any way to achieve that? Thanks and greetings Udo Udo wrote: > Hello, > > I want to delete a certain area of ALL pages of an pdf-file. > Unfortunately with trimming the margins that doesn't work, > because the content is only hidden and so still existing. > Is there any way to achieve that? > > > Thanks and greetings > Udo Do you mean something like Mask-It? h...

(",) Do You Want To Know For Sure You Are Going To Heaven? #2
http://www.want-to-be-sure.blogspot.com << Click On Link ...

Checking hash tables values in multithread environment
hi list, i'm trying to create a system where i have a producer-consumer paradigm but with threads. Now i already created a mutex. My mainly problem is that the producer get output from many hosts and i have to store and check if i have, for each host, the same number of values collected. Now i think to use an Hash where i have ip_address as key and as value the array of values collected by Producer. If the number of values for an host is reached, the consumer have to do something. This check work with a simple array but for multiple host, i dont know how to do it. Ki...

Library help wanted...
So I've got this compiler. It's the ACK, a BSD-licensed multitarget, multiplatform compiler suite dating from a few years ago, that I'm slowly knocking into shape. It's small, it's fast, it's got its own libraries, and while it doesn't generate the best code in the world it's more than adequate for most purposes. http://tack.sourceforge.net/ Modula-2 is one of the many languages that it supports. I got the compiler working, and then learnt enough of the language to write a demo program and make it go. However, I am fundamentally not a Modula-2 prog...

How to prevent deleting a file
Hello, Is there any VC++ function that I can use to prevent deletion of a file? Do I need to change the security flags of the file? Thanks Pada 1. On NTFS you may alter the file permissions 2. In general you could try either of: * write a file system filter driver to prevent a given file name's deletion * open the file exclusively and keep it open -- Elias "pada" <paymand@hotmail.com> wrote in message news:1169082572.217049.134300@v45g2000cwv.googlegroups.com... > Hello, > Is there any VC++ function that I can use to prevent deletion of a > ...

Wrong glue records entered.
Are glue records supposed to be returned with the MX records? The problem that we are having is that someone will create the following MX records for their domain. @ 10800 IN MX 40 smtp.secureserver.net. But then some one else will create the domain "secureserver.net" in our system and point the A record for "smtp" to another IP. Now "secureserver.net" is not pointing to our name servers (at the root name server level) so our servers should never be asked for it. But they are by some resolvers and it is poisoning everything. ...

Re: proc sql outer join with more than two tables #2
On Sat, 10 Jan 2009 14:43:05 -0500, Phil Spence <linux70@COMCAST.NET> wrote: >Could someone do me a huge favor and post some code that shows a left outer >join with more that two tables. All the examples and documentation show >examples with just two tables. I need to join 4 tables with an outer join >between on just one of the tables. Possible? * Sampe data ; proc sql feedback _method ; create table class as select name , age from sashelp.class ; create table weight as select name , weight from sashelp.class where name let 'O...

Separated the noise
dear who considering i write my simple code under my simple information of matlab function and post the code in order to benefit other beginner thanks I have a problem and I hope any help for me how to separated the original signal from its noise. I have a wave(.wav) I blend it with noise. my listing program : y=wavread(open.wav); spect=abs(fft(y,1024)); frek= linspace(0,22050,512) plot(frek,spect(1:512)); % generate and mix the noise. open_noise=agwn(y,20); Thank you. On Dec 25, 11:13=A0pm, "Muhammad " <fahrudin_fis...@yahoo.com> wrote: > dear who considering > &...

Keep running out of memory
I am doing audio simulations using Finite Difference Time Domain methods. This requires 4 very large arrays. So far I have been utilizing 10cm grid spacings in regualr sized rooms which require arrays with about 100,000 points. But I want to use smaller grid spacings like 1cm which would require arrays of 100,000,000 points. My desktop runs out of memory at about 14,000,000 after upping my virtual memory to 20gigs. I can't imagine I should keep upping my virtual memory so is there another way around my "out of memory" problem ? "Ryan Matheson" <ryan.j.matheson@gma...

I want to be convinced; convince me.
Several people I know like Macs. A lot. I use Windows. I don't like it a lot. (Parse that either way -- both work.) I want to want to move to a Mac, but I'm having trouble convincing myself to do it. Today I visited an Apple store for the 2nd or 3rd time to talk about getting a mini to add to my Windows network, and for the 2nd or 3rd time I came away with less enthusiasm for the Mac than before I went there. However, I worry that the people who work there aren't terribly well versed in the products they sell, because they often can't answer my questions. I'm hoping...

vector, hash table, etc. library in C? I am abandoning C++
Hello, I regret to say that C++ sucks big rocks. I used it until recently because of its container libraries and for no other reason. In a nutshell concurrency, interruptions, exceptions are ingredients which when mixed according to a C++ recipe produce a disaster. Today I searched for a pure C implementations of vector, dictionary, and other data structures on the net but the results contained so much C++ noise that it's like looking for a needle in a haystack. Can anybody please point me to such a true and tried C library? Much obliged --. Markus Neuhaber mneuhaber22@berlin.com wrot...

Wanted: Computer Gaming World issues 2.2, 2.4, 4.1
Good day, I am looking for the following issues of Computer Gaming World magazine: 2.2, 2.4 (1982) 4.1 (1984) I'm willing to purchase or trade... thanks for your help! Steph -- cgw.vintagegaming.org The Computer Gaming World Museum ...

python GUIs comparison (want)
Now i began to learn GUI programming. There are so many choices of GUI in the python world, wxPython, pyGTK, PyQT, Tkinter, .etc, it's difficult for a novice to decide, however. Can you draw a comparison among them on easy coding, pythonish design, beautiful and generous looking, powerful development toolkit, and sufficient documentation, .etc. It's helpful for a GUI beginner. Thank you. :)Sorry for my poor english. jiang.haiyun@gmail.com wrote: > Now i began to learn GUI programming. There are so many > choices of GUI in the python world, wxPython, pyGTK, PyQT, > Tkinter...

Need help deleting file with bad filename
I don't know how it happened, but I have a file on the Windows 2000 Professional machine that has a colon in the name itself. Nothing I've tried with Windows Explorer will touch the file. I tried a few wildcard tricks from the command line, but couldn't get that to work either. Any ideas on how to delete the file? TIA Tom "Tom Bates" <noneedto@email.me> wrote in message news:4igugv46usnuuqp09i9kijc7gtb7l1alvg@4ax.com... > I don't know how it happened, but I have a file on the Windows 2000 > Professional machine that has a colon in the nam...

Looking for SQL (Delete Query) that will delete records in table1 that are in table2
Hi, I have in table1 ten records: 1 2 3 4 5 6 7 8 9 10 In table2 I have 6 records. 2 3 5 6 8 9 I want to delete the records from table1 that are in table2. So after the Delete Query runs I want table 1 to have records: 1 4 7 10 What is the Delete query (SQL) that will accomplish this? Thanks Barry Hi, found this answer in another post: DELETE table1.ID FROM table1 WHERE (((table1.ID) In (SELECT ID FROM [table2];))); On Saturday, 27 September 2014 07:10:55 UTC-6, Barry wrote: > Hi, > > > > I have in table1 ten records: > &...

I want to share with you one of my prefered songs...
Hello... I want to share with you one of my prefered songs... Here is the Boney M Greatest Hits Non Stop: http://www.youtube.com/watch?v=UaykrDs79kI Amine Moulay Ramdne. On 5/7/2014 8:16 PM, axman6@gmail.com wrote: > On Thursday, 8 May 2014 03:20:36 UTC+10, aminer wrote: >> Hello... >> >> I want to share with you one of my prefered songs... >> >> Here is the Boney M Greatest Hits Non Stop: >> >> http://www.youtube.com/watch?v=UaykrDs79kI >> >> Amine Moulay Ramdne. > > This nonsense is completely irrel...

Business partner wanted
I'm looking for a Business Partner for my Mid-Michigan Shop to grow and take to the next level. Here's what I have to offer. I have over 20 years as a Journeyman Tool and Die Maker. Last 10 years as the owner of my own business. I design, build and run injection molds. I have experience with stamping dies, fixtures and gages. Product developement, CAD, CAM, CNC, EDM and custom machining. The shop is well equipt 2400 sq/ft with 600 sq./ft office space located at the end of a quite street visible from US-10 hwy. Area is well known in the thermoforming industry. The equipment is Haas ...

Can I EXPORT tables to a backup DB and delete them from my production server?
I have a database file approximately 30GB in size which creates 3 data tables daily. I need to reduce the size of the DB due to disk size limitations. Could I EXPORT some tables, let us say over thirty days old, to something like DB_Name_Archieve and them delete them from the production DB to reduce the size? I also need the ability to reference/read them at a later time as needed. As a side note, could SQL Server use data on a redirected drive i.e. NET Used. This would allow me to move the old tables to the redirected drive and reference them as needed. As always, thanks in advance. Pe...

Regarding 3-D lookup table
Hi, I want to create 3-D lookup table. Here i have 3 input with different dimensions. Ex: a=[1 2 3]; b=[2 3 4] and c=[1 4]. output z contains 18 elements. How can give the output data for 3 -d lookup table. Regards Kamalakar On 12/12/10 6:04 AM, kamalakar wrote: > I want to create 3-D lookup table. Here i have 3 input with different > dimensions. > Ex: a=[1 2 3]; b=[2 3 4] and c=[1 4]. output z contains 18 elements. How > can give the output data for 3 -d lookup table. ismember() against the allowed inputs to determine the indices; use the indices to index in t...