Access: delete a record and insert it in another tab

[Wguy2008]

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

[XSI]

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.

[Wguy2008]

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

[XSI]

You do:

Update table1 set amount_1 = (select sum (amount_2) from table2 where condition)
,amount_2 = 0

[fellah]

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 ...)!

[Ashok.M]

Something like this should run:

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