Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am trying to write a delete query where it looks at the Consignment location table and deletes matching data out of the QOH table based on the Location and BU #. I keep getting a specify with table for the data to be deleted error. The issue is that some of the BU#s have the same Location. For example both BU#s 101 and 125 have location AA9 but only BU 101 is listed on the Consignment Location table so that is the only data I would want deleted I would still want BU 125 location AA9 to remain on the QOH table. Originally I had a simple delete query with the following:

    Field: Loc
    Table: QOH
    Delete: Where
    Criteria "In (SELECT [Loc] FROM [Consignment Locations])"

    The issue is that with the original query it was deleting the data for both BUs 101 and 125. I tried to modify the criteria line to include the BU# but it didn't work. I then tried to create another query where in the criteria line i had [QOH]![Loc]=[Consignment Location]![Loc] and a second one for the BU, but this is where I got the specify which table.

    Any assistance would be greatly appreciated.

    Thanks

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Midwest, USA
    Posts
    108
    Thanks
    5
    Thanked 5 Times in 4 Posts
    Hi,

    I would suggest building the query in Access's query builder as a select query (so you don't lose any data). Add all of your criteria there where you can play with it. Once the query is returning the correct result, then change it to a delete query. Once you have it working go to View and change it to sequel view to see the proper code.

    John

  3. #3
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I tried your suggestion and recieved the error "Could not delete from specified table". Attached is a snapshot of what the select query looks like and the SQL when I changed it to a delete query. I need it to become a delete query where the results are removed from the QOH table. Any other suggestions? This really has me stumped.
    Attached Files Attached Files

  4. #4
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I was able to do a work around. I created an update query that combined both the Loc and BU #. I was then able to use my original delete query. Thanks for your response, I appreciate it.

  5. #5
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Midwest, USA
    Posts
    108
    Thanks
    5
    Thanked 5 Times in 4 Posts
    I'm glad you worked it out.

Posting Permissions

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