Help! Synchronizing Two Fields (DAO)...

I want to compare two fields (A & B) in an Access Database. I want to add all the records in field A to Field B and delete all the records in B that are not in A. Pretty simple but I haven't had any luck. Field A is the master and is always changing. I want to make B do the same as A without clearing the existing records and refilling all the records in B.


This fills B:

Code:

---

With rstDownEquipF1            'Copy temp to Queue
        rstTempF1.MoveFirst
        Do While Not rstTempF1.EOF
            .FindFirst "EquipQIDF1 = '" & rstTempF1!TempQF1 & "'"
            If .NoMatch = True Then
                .AddNew
                !EquipQIDF1 = rstTempF1!TempQF1
                .Update
            End If
        rstTempF1.MoveNext
        Loop
    End With

---

I can't figure how to delete the extra records from B.
Thanks
Rel

Do you mean table A and B vs field A and B?

On the addition part are there records that are in table A that are not in table B that you want to move over?

Select * from Table A WHERE FieldA NOT IN (Select Field B From TableB)
do while not rst.eof
'Insert record code here....
rst.movenext
loop

You can do the deletion pretty easy with a query...

Delete from TableB Where Fieldb NOT IN (Select FieldA From TableA)

Not exactly sure if that is what you are looking for, because I am not quite understanding the problem.

Thanks, Doug, for the reply and suggestions. I have two Tables (tX and tZ) in one database. I want to sync one field (A) from tX and one field (B) from tZ. I'll try using the DELETE statement. Thanks

Rel

Edit:
Some help with DELETE syntax, i.e., how to designate field names and how to build the string would be a great help.

What do you mean designate fieldnames?

taking from your example below...

If you want to delete all records from table TZ where the records in Field B do not match the records in table TY field A... this would be...

DELETE FROM TZ WHERE FIELDB NOT IN (SELECT FIELDA FROM TY).

You can do a select query first to make sure that you are getting the correct records before you delete...

SELECT * FROM TZ WHERE FIELDB NOT IN (SELECT FIELDA FROM TY).


If you give me exact tablenames and fieldnames I could write exact query for you.

Thanks Doug... your are too kind. This is what I have:

Code:

---

    Set rstDownEquipF1 = dbPageMe.OpenRecordset("EquipQF1", dbOpenDynaset)
    Set rstTempF1 = dbPageMe.OpenRecordset("TempEquipQF1", dbOpenDynaset)

' Some Loop   

dbPageMe.Execute "DELETE FROM " & "TempEquipQF1 WHERE EquipQIDF1 NOT IN 'rstTempF1!TempQF1';"

' Loop

    rstDownEquipF1.Close
    rstTempF1.Close

---

I am SQL illiterate. I get error 3075: In operator without () in query expression 'EquipQIDF1 NOT IN 'rstTempF1!TempQF1'.

Thanks
Rel

Ok,

Your two tables are....
EquipQF1 and TempEquipQF1

What are the fieldnames in each table?

It should look more like this...


"DELETE FROM TempEquipQF1 WHERE TempEquipQF1_FieldName NOT IN (select EquipQ1DF1_FieldName FROM EquipQIDF1)"

soo, if my EquipQF1 has a field called TEMPA
and my TempEquipQF1 has a field called TEMPB then it would look like this...

"DELETE FROM TempEquipQF1 WHERE TEMPB NOT IN (select TEMPA FROM EquipQIDF1)"

OK I got it ! Thanks you very much.

Relentless