Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2004
    Posts
    9

    Unanswered: Delete Query Trouble!! Plz Help =)

    Hi there,

    I am having a few problems. I get the error that my query "Could not delete from specified tables." Now the files are not Read only and I am the Admin of both of the tables and DB. Here is what I am trying to do and here is what I got.

    Table 1:

    First, I have a Table that has alot of records in it. That table is called "Cancellation Sheet". Within that table there is a column that is named "Job #". Quotes are to just seperate to show you better.

    Table 2:

    Next, I have a Table that is Named "Cancellation Entry". It only has one column named "Job #".

    What is needed to happen:

    What I want to happen is this. I want to be able to type into Table 2 which is "Cancellation Entry" into the "Job #" field and when done typing in the job numbers into it, run the delete query to get rid of the Records on the "Cancellation Sheet" that are the same as the Job #'s I just typed into the "Cancellation Entry" Table.

    Here is the code I got when I look at the SQL view in Access xp or 2002.

    DELETE [Cancellation Sheet].*
    FROM [Cancellation Sheet], [Cancellation Entry]
    WHERE ((([Cancellation Sheet].[Job #])=[Cancellation Entry]![Job #]));


    Now From that code I get the Error: Could not delete from specified tables. And then I am confused.

    So what I tried to do was this:

    DELETE [Cancellation Sheet].*
    FROM [Cancellation Sheet]
    WHERE ((([Cancellation Sheet].[Job #])=[Cancellation Entry]![Job #]));

    Note the missing part I took out from the 2nd Line. I removed the entry [Cancellation Entry] part of it. When I do that, I get a pop up box asking me the parameter. So I typed in a Job Number that I knew was on the [Cancellation Sheet] table into the parameter box and poof it asked me if I wanted to delete 1 record. I said Yes and it deleted it. So now I am really confused. I cannot type every Job number into a parameter box so I figured I would try it the way I showed up top in the first Code posted. Please if you can help me please do. I hope I have given enough information for this. Anymore information needed please do not hesitate to ask. I am just trying to enter numbers in a table and compare them to another and get rid of them. Than what is remaining Print a report out of those left.

    Thanks in advance for any help you all may be.

    Sincerely,
    Lopdrop

  2. #2
    Join Date
    Jan 2004
    Posts
    492

    Re: Delete Query Trouble!! Plz Help =)

    Try taking out the .* part of the query:

    The syntax for a delete should be like so:

    Code:
    delete from
    table_name
    where blah...blah
    You dont explicitly name the columns, because a delete statement clears the entire record, so it wouldnt make sense to name the columns. I'm guessing thats where you are running into problems. Try it and let me know

  3. #3
    Join Date
    Mar 2004
    Posts
    9

    Re: Delete Query Trouble!! Plz Help =)

    I took out the .* in the code resulting in this:

    DELETE [Cancellation Sheet]
    FROM [Cancellation Sheet], [Cancellation Entry]
    WHERE ((([Cancellation Sheet].[Job #])=[Cancellation Entry]![Job #]));


    When this query is run I get the result:

    Specify the table containing the records you want to delete.

    That is error 3128.

    That is the part you wanted me to delete correct?

    Any other Ideas Friend? All of them are most useful.

    Thanks again.

    LopDrop

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Re: Delete Query Trouble!! Plz Help =)

    [SIZE=1]Originally posted by LopDrop

    WHERE ((([Cancellation Sheet].[Job #])=[Cancellation Entry]![Job #]));
    WHERE ((([Cancellation Sheet].[Job #])=[Cancellation Entry].[Job #]));

    ps. error 3128 is "Must specify tables to delete from" error.

  5. #5
    Join Date
    Jan 2004
    Posts
    492

    Re: Delete Query Trouble!! Plz Help =)

    You are not allowed to delete from more than one table in a regular SQL statement. You'd have to write a script to allow for more than one deletion, or just issue 2 deletion queries separately.

    You would have to delete from one table, and then write another statement to delete from a 2nd table. SQL itself does not have the functionality to allow multiple table deletes.


    I guess I was not clear in my first post even though I gave the syntax.
    You do NOT explicitly name any columns to delete from. You just give the table name and where clause - it doesnt matter what columns you are deleting from, b/c you are deleting an entire row.

    You would want:

    Code:
    DELETE
    FROM [Cancellation Sheet]
    WHERE ((([Cancellation Sheet].[Job #])=[Cancellation Entry]![Job #]))
    I'm assuming that your WHERE clause is a valid one - I'm not checking whether it is or isn't. But this is the syntax you want to follow. You would then issue another SQL statement with this same format, but delete from Cancellation Entry instead.

    Hope this helps!

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    that's what I was getting at... I don't think the where clause is correct. My hunch is he needs to change the ! to a .

  7. #7
    Join Date
    Mar 2004
    Posts
    9
    OK, I tried:

    DELETE
    FROM [Cancellation Sheet]
    WHERE ((([Cancellation Sheet].[Job #])=[Cancellation Entry]![Job #]));

    And it wants me to enter a parameter value upon running the query.

    I am not trying to delete from the actual [Cancellation Entry] Table. That one will always be manually deleted. I just need that to enter in the Job #'s that need to be stripped off the [Cancellation Sheet]. So just one table needs to be deleted from. Maybe I am going about this wrong.

    This is so frustrating. It seems so easy but than it has me stumped. Still looking to get it to work. So any help is still much appreciated.

    LopDrop

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by LopDrop
    OK, I tried:

    DELETE
    FROM [Cancellation Sheet]
    WHERE ((([Cancellation Sheet].[Job #])=[Cancellation Entry]![Job #]));

    And it wants me to enter a parameter value upon running the query.

    I am not trying to delete from the actual [Cancellation Entry] Table. That one will always be manually deleted. I just need that to enter in the Job #'s that need to be stripped off the [Cancellation Sheet]. So just one table needs to be deleted from. Maybe I am going about this wrong.

    This is so frustrating. It seems so easy but than it has me stumped. Still looking to get it to work. So any help is still much appreciated.

    LopDrop
    Did you try changing your "!" to a "." yet?

  9. #9
    Join Date
    Mar 2004
    Posts
    9
    Yes I Did change that. Either way I do that with the "!" or the "." it results in the same.

    LopDrop

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Well in that case..

    what is: [Cancellation Entry]?

    It's obviously not a table if you're still getting that error. If it's a form then you need to be explicit:

    WHERE ((([Cancellation Sheet].[Job #])=[forms]![Cancellation Entry]![Job #]));

  11. #11
    Join Date
    Mar 2004
    Posts
    9
    [Cancellation Entry] is a table that has the typed in Job #'s that are to be stripped from [Cancellation Sheet]. What is strange is because if the parameter box pops up for me to put a value in it. I can type something in it and if it matches a value from the [Cancellation Sheet] table than it deltes it successfully.

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    *shrug*

    something is wrong with your syntax if this doesn't work:

    [Cancellation Entry].[Job #]

    Try to create a new query of:

    SELECT [Job #]
    FROM [Cancellation Entry]

    If that doesn't work, you've got a problem.

  13. #13
    Join Date
    Mar 2004
    Posts
    9
    Ok let me try that, and again I really appreciate your time you are helping me friend.

  14. #14
    Join Date
    Mar 2004
    Posts
    9
    Ok, I ran that query and it pulls up the Job #'s that are typed into the table [Cancellation Entry].

    So that did work there. So it is a table. Now to get it to pull these numbers off of the [Cancellation Sheet] Table.

    SELECT [Job #]
    FROM [Cancellation Entry]

    Is what I ran.

  15. #15
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    There is something dumb that I'm missing here...

    *sigh*

    Maybe you want to AIM me and I could bounce some more diagnostics off of you? AIM is in profile

Posting Permissions

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