Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 02-04-2009
Member
 
Join Date: Nov 2008
Posts: 1,001
Access: delete a record and insert it in another tab

Here is the problem in VBA/SQL access:

I have two tables years and year-1, the same structure, containing records

At the turn of the year I have under certain closing conditions (fairly simple to code in a WHERE), delete records from the table and add years to the table year-1 which were first cleared (by the way how to VBA: Delete simply?)

Years the table contains only a few hundred records and only a hundred must switch to years-1
Reply With Quote
  #2  
Old 02-04-2009
XSI XSI is offline
Member
 
Join Date: May 2008
Posts: 271
Re: Access: delete a record and insert it in another tab

I do not know VB, but I know you can use SQL to solve your problem. For the insertion of data into the table year-1 should be this:

Insert into years-1 (field1, field2, fieldn)
Select field1, field1, fieldn from years;

After the removal of the years table data you do:

Delete from years.
Reply With Quote
  #3  
Old 02-04-2009
Member
 
Join Date: Nov 2008
Posts: 1,001
Re: Access: delete a record and insert it in another tab

Thank you for the walkthrough, it should actually proceed in two stages since the delete command does not allow access to records deleted. One last question for the road

In a table if I want to make the sum of amount_2 in amount_1 then save amount_2 to 0 is what I can do a single update like:

update .... set amount_1 = amount_1 + amount_2, amount_2 = 0
Reply With Quote
  #4  
Old 02-04-2009
XSI XSI is offline
Member
 
Join Date: May 2008
Posts: 271
Re: Access: delete a record and insert it in another tab

You do:

Update table1 set amount_1 = (select sum (amount_2) from table2 where condition)
,amount_2 = 0
Reply With Quote
  #5  
Old 02-04-2009
Member
 
Join Date: May 2008
Posts: 685
Re: Access: delete a record and insert it in another tab

Quote:
Originally Posted by Wguy2008 View Post
Thank you for the walkthrough, it should actually proceed in two stages since the delete command does not allow access to records deleted. One last question for the road

In a table if I want to make the sum of amount_2 in amount_1 then save amount_2 to 0 is what I can do a single update like:

update .... set amount_1 = amount_1 + amount_2, amount_2 = 0
yes, you can, it works. Well, before testing on a set of test, but there's no reason why it does not (whatever ...)!
Reply With Quote
  #6  
Old 02-04-2009
Member
 
Join Date: Feb 2008
Posts: 194
Re: Access: delete a record and insert it in another tab

Something like this should run:

Code:
Sub foo () 
Guil = Chr(34) 
DoCmd.SetWarnings False 
'Add a table Year-1 
RSQL = "INSERT INTO [Year-1] (YourYearID-1)" & _ 
"SELECT Year.* Year.YourYearID" & _ 
"FROM Year" & _ 
"WHERE (((Year.YourFieldFilter) =" & Guil & "YourFilter" & Guil & ")); 
DoCmd.RunSQL RSQL 
'Remove the table Year
RSQL2 = "DELETE DISTINCTROW Year.*, [Year-1]. YourFieldFilter" & _ 
"FROM Year LEFT JOIN [Year-1] ON Year.YourIDYear = [Year-1]. [YourIDYear2-1]" & _ 
"WHERE ((([Year-1].[YourIDYear2-1]) Is Not Null)); 
DoCmd.RunSQL RSQL2 
End Sub
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Access: delete a record and insert it in another tab"
Thread Thread Starter Forum Replies Last Post
Delete record from a java interface Gunner 1 Software Development 5 19-01-2010 10:56 AM
Unable to delete record from gridview in C# MAGALY Software Development 4 18-01-2010 10:56 PM
Microsoft access do not save changes when navigating to next record Joko Software Development 3 29-08-2009 11:00 AM
Insert image in access database from vb 6.0 Vireshh Software Development 2 22-01-2009 09:06 PM
Delete this record when it becomes stale Magnus Kirkerud Windows Server Help 1 19-04-2007 09:32 PM


All times are GMT +5.5. The time now is 10:35 AM.