Tracking changes

[Dina01]

Hello,

I have a table named data_tb some of the fields of the table are:

DataID
Date
Agent
Code

What I would like to do is that when there is records in data_tb, that the code is equal to 4. I want to create a new table that only shows all the records that have code =4. And then I want my original table data_tb to delete all records with that code =4.

Can anyone tell me how to do this...

Thanks

 

[cainemart]

Use a make table query and use the criteria of 4, this way all records with a code of 4 will be pulled into the new table, then use a delete query with the same criteria to remove them from the original.

CM

 

[LittleSmudge]

Use the Make Table Query:-
"SELECT DataTbl.* INTO NewDataTbl
FROM DataTbl
WHERE DataTbl.Code = 4;"

Then you could
A) Use another MakeTable Query:-
"SELECT NewDataTbl.* INTO DataTbl
FROM DataTbl
WHERE DataTbl.Code <> 4;&quot;

Then delete the old table and rename the new one

OR
B) Open a Recordset based on DataTbl.Code = 4 and delete the records

Eg.
Dim rst As New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
rst.Open &quot;SELECT DataTbl.* FROM DataTbl WHERE DataTbl.Code = 4;&quot;
If Not rst.EOF Then
rst.Delete AcAffectGroup
End If
rst.Close




QED?

G LS

 

[Dina01]

Ok, I have done that before but the problem is that after I run my delete qry, all the records with code 4 from the original table delete, that is perfect.

And my backup table is perfect, but then if I run the Make-Table qry again, what happens is that if there is no code =4 in the original table then it will also disappear in my backup table.

I can't have that, because what I need is a backup table that keeps track of all data, with code =4.

 

[netconfused]

Have you tried using an Update query once the code 4 table is intially created?
Glenda

 

[LittleSmudge]

Okay Dina01 - I miss understood the initial problem.

Then create the NewDataTbl in the required format but with no records then use an Append Query instead of a MakeTable Query.

&quot;INSERT INTO NewDataTbl
SELECT DataTbl.*
FROM DataTbl
WHERE DataTbl.Code = 4;&quot;



'ope-that-'elps.

G LS

 

[Dina01]

Thank You,

That worked great..........