I am rather new when it comes to access and am just starting to learn things so I apologize if this is a completely newb question here. I have a access database tied to sharepoint and I want to avoid updating all items every day as that takes hours. As such, I am creating 2 queries to remove and add just changed items every day. Basically it does the following...
Table1 - Unfiltered and unformated table.
Table2 - Sharepoint table
qry1- Filters and aligns to data to the format needed from Table 1.
qryAdd - Finds new items in qry1 and finds that they are unmatched in Table2
qryDel - Finds items in Table2 that no longer exist in qry1and delete from Table2
All items work correct, with the exception of qryDel as I get an error message saying I must specify the table... which I can't figure out. I can run the view and it shows the right results but it throws the error when deleting. The SQL from this qry are as follows...
DELETE [Training Completion].*, qryFiltered.MatchAll
FROM [Training Completion] LEFT JOIN qryFiltered ON [Training Completion].MatchAll = qryFiltered.MatchAll
WHERE (((qryFiltered.MatchAll) Is Null));
If each row in [Training Completion] has a unique identifier (UID), you could try (air code, not tested):
DELETE [Training Completion].*
FROM [Training Completion]
WHERE [Training Completion].UID IN (
SELECT [Training Completion].UID
FROM [Training Completion] LEFT JOIN
qryFiltered ON [Training Completion].MatchAll = qryFiltered.MatchAll
WHERE (qryFiltered.MatchAll Is Null)
However, I was actually wrong... the add doesn't seem to want to work either I have tried multiple ways and this seems to be closest but it doesn't want to add my MatchAll field and maybe I don't even need these stupid AssociateItemID and MatchAll fields but it was my best bet since I am a newb and don't know a better way. Basically my idea was for add and delete was to check..MatchAll and AssociateItemID fields... and if the AssociateItemID ([Associate Name]&[Item Id]) existed on the table, but the MatchAll([Associate Name] & [Item Id] & [Course Attendee Status] & [Enrolled Date] & [Course Due Date] & [Completion Date] & [Course Assigned Flag]) field did not match then one of the items had changed and it needed to delete and then add the new row.