Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2011
    Posts
    4

    Unanswered: Delete Query error Must specify table

    Hello,

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

    I attached what the query looks like also.

    Thank you

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If each row in [Training Completion] has a unique identifier (UID), you could try (air code, not tested):
    Code:
    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)
    );
    Have a nice day!

  3. #3
    Join Date
    Dec 2011
    Posts
    4
    Thank you, that worked great!

    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.

    Am I going about this all the wrong way?

    INSERT INTO [Training Completion] ( [Manager Name], [Associate Name], [5-Dot Hierarchy Code & Description], [Item Id], [Course Title], [Course Attendee Status], [Enrolled Date], [Course Due Date], [Completion Date], [Course Assigned Flag], AssociateItemID, MatchAll, AssociateItemID )
    SELECT qryFiltered.[Manager Name], qryFiltered.[Associate Name], qryFiltered.[5-Dot Hierarchy Code & Description], qryFiltered.[Item Id], qryFiltered.[Course Title], qryFiltered.[Course Attendee Status], qryFiltered.[Enrolled Date], qryFiltered.[Course Due Date], qryFiltered.[Completion Date], qryFiltered.[Course Assigned Flag], qryFiltered.AssociateItemID, qryFiltered.MatchAll, [Training Completion].AssociateItemID
    FROM qryFiltered LEFT JOIN [Training Completion] ON qryFiltered.[AssociateItemID] = [Training Completion].[AssociateItemID]
    WHERE ((([Training Completion].AssociateItemID) Is Null));
    Last edited by Dulanic; 12-16-11 at 11:29.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •