Small assistance with my Delete query please

[Deirdre Marie]

I am using a linked table from another database and a "local" table to compare data. The linked table always has the correct information so I am utilizing append and delete queries to ensure my local one stays up to date as well.

These tables record upcoming community type events. The linked table automatically assigns a number to the event and I use that number to compare the two tables. If a number exists in my local table but not the linked, it's deleted. If a number does not exist in my local, but does exist in the linked, it's added.

Easy!

However, I've now discovered a problem in that sometimes information is changed within the event and the append /delete queries need to be re-written in order to catch this.

Original Information
ID Date Event
100 2/1/03 Firehouse Carnival

New Information
ID Date Event
100 2/9/03 Firehouse Carnival

As written, the append and delete queries wind up keeping both as the ID Number has not changed. I deceided to edit the original SQL so it will look at the ID AND the date and delete anyhting from my local table that does not match the linked table. Unfortunately, it is not working, and I need a little assistance.

DELETE Projections.*, ProdPlan.ID, ProdPlan.ID, ProdPlan.DATE
FROM Projections LEFT JOIN ProdPlan ON Projections.ID = ProdPlan.ID
WHERE (((ProdPlan.ID) Is Null)) OR (((ProdPlan.ID)<>[Projections].[ID]) AND ((ProdPlan.DATE)<>[Projections].[DATE]));


Thank you in advanced!

 

[FoFa]

If the data is not really huge, why not just go for a total delete and repopulate each time?
Of course if you are using a linked table, why not just read the linked data (inquiring minds want to know).
If those are not good, you would have to check for mismatches on fields that concern you, and update those rows instead. As an example we have something like that here, and we have a DelFlag on the table, we have about 12 queries that go through and detect no matches, and check fields for no matches and set that flag. Then the delete runs and deletes all those with the flag set, and then the update runs repopulating data that does not exist.

 

[Deirdre Marie]

Thank you for the reply.

I do not have the entire table deleted for it is used to store different bits of information the original linked table does not have (e.g. details of the events). The original linked table is simply used for the ID, the date, and the name of the event.

I briefly read about "mismatch" queries as I was searching for an answer to this problem, but unfortunately did not see any information on how to create them in my ACCESS reference book here in the office.

Perhaps you might be able to point me in a better direction regarding "mismatch" queries and I would be happy to research the answers to my dilema.

Thanks much!