Help! Synchronizing Two Fields (DAO)...

[Relentless]

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:

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

[Doug10101]

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.

[Relentless]

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.

[Doug10101]

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.

[Relentless]

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

    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

[Doug10101]

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)"

[Relentless]

OK I got it ! Thanks you very much.

Relentless