archiving data
in [Forms]
|
Prev: FRUSTRATED: Enable field depending on value of another
Next: How to Show Form ComboBox value into other Form Label caption
From: Daniel M on 19 May 2010 22:59 I have a spreadsheet that lists serial numbers. I can import that into a tmp table in access, but what i need to do is search my main serialnumber table and find all instances of the data and append it to an archive table and then delete the data from the main table. I have a query that inner joins a temp table and a main table and updates the phone number from the temp table to the main table. I was thinking something like this but i dont know exactly how to do it. any help? I would like to put this behind a button. thanks.
From: Mike Painter on 20 May 2010 00:40 Daniel M wrote: > I have a spreadsheet that lists serial numbers. I can import that > into a tmp table in access, but what i need to do is search my main > serialnumber table and find all instances of the data and append it > to an archive table and then delete the data from the main table. All instances of what data? The records that match the Excel information or??? In any event unless you have a HUGE table, it is probably easier to just add an "Archive?" field to your table and base your foms and reports on fldArchieve = False
From: Daniel M on 20 May 2010 23:22 Let me backup and start again. I have a main table with several thousand records with fields ID, Serialnumber, TelNumber, Serialnumber2,Location. We are currently deactivating some units and reactivating them with new serialnumbers and telnumbers. I dont want to keep the old records in the table as they are obsolete and shouldnt be needed every again. But you never know, so i dont want to just delete them either. As i deactivate them i get a spreadsheet with serialnumber, telnumber,serialnumber2. I would like to look up those values in the main table and export them along with the other fields to an archive table. then delete them from the main table. "Mike Painter" wrote: > Daniel M wrote: > > I have a spreadsheet that lists serial numbers. I can import that > > into a tmp table in access, but what i need to do is search my main > > serialnumber table and find all instances of the data and append it > > to an archive table and then delete the data from the main table. > > All instances of what data? > The records that match the Excel information or??? > > In any event unless you have a HUGE table, it is probably easier to just add > an "Archive?" field to your table and base your foms and reports on > fldArchieve = False > > > . >
From: PieterLinden via AccessMonster.com on 21 May 2010 00:55 Daniel M wrote: >Let me backup and start again. > >I have a main table with several thousand records with fields ID, >Serialnumber, TelNumber, Serialnumber2,Location. > >We are currently deactivating some units and reactivating them with new >serialnumbers and telnumbers. I dont want to keep the old records in the >table as they are obsolete and shouldnt be needed every again. But you never >know, so i dont want to just delete them either. > >As i deactivate them i get a spreadsheet with serialnumber, >telnumber,serialnumber2. I would like to look up those values in the main >table and export them along with the other fields to an archive table. then >delete them from the main table. > >> > I have a spreadsheet that lists serial numbers. I can import that >> > into a tmp table in access, but what i need to do is search my main >[quoted text clipped - 9 lines] >> >> . so what's your question? standard way of doing an archive is something like Begin Transaction append archived records to archive table (Use DBEngine(0)(0).Execute AppendQueryName) delete archived records from active table (Use DBEngine(0)(0).Execute DeleteQueryName) End Transaction just make sure the two queries have the same where clause. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1
From: Mike Painter on 22 May 2010 15:04
Daniel M wrote: > Let me backup and start again. > > I have a main table with several thousand records with fields ID, > Serialnumber, TelNumber, Serialnumber2,Location. > > We are currently deactivating some units and reactivating them with > new serialnumbers and telnumbers. I dont want to keep the old records > in the table as they are obsolete and shouldnt be needed every again. > But you never know, so i dont want to just delete them either. > > As i deactivate them i get a spreadsheet with serialnumber, > telnumber,serialnumber2. I would like to look up those values in the > main table and export them along with the other fields to an archive > table. then delete them from the main table. I'm going to guess that this is a manual process and that you use a Boolien field to deactivate them. Build an append query to your archieve table and append for Deactivate = True. Then run a delete query on the main table with the same criteria. Alternatively you could simply change the serial number and TelNumber in the table. This assumes, and is one good example of why, a serial number is rarely a good key field. I also makes the assumption that noody ever uses an old serial number for any reason. > > > "Mike Painter" wrote: > >> Daniel M wrote: >>> I have a spreadsheet that lists serial numbers. I can import that >>> into a tmp table in access, but what i need to do is search my main >>> serialnumber table and find all instances of the data and append it >>> to an archive table and then delete the data from the main table. >> >> All instances of what data? >> The records that match the Excel information or??? >> >> In any event unless you have a HUGE table, it is probably easier to >> just add an "Archive?" field to your table and base your foms and >> reports on fldArchieve = False >> >> >> . |