Results 1 to 11 of 11
  1. #1
    Join Date
    May 2009
    Posts
    104

    Unanswered: Another Delete query problem, great!!

    I have a table in my database that tracks items being logged in and out of our QC lab. What i want to do is after something has been checked back "in" i want to delete both records that were made for that particular item. What i have done is create a query that gathers all the item numbers that have "CHECKED IN" in the transaction type field. Then i created another query that found the matching part numbers. I can get the query results that i want but when i go to design mode and select "delete" query type and select run it tells me i haven't selected a table to delete the records from. I made a delete query before just to play around with them in another database and i didn't have to specify what table, i guess it assumed the correct one. I can probably make a delete duplicate records query but i would rather have it delete only items that have been checked back in. Any help would be greatly appreciated, my brain is hurting. If anyone needs any additional information i can post what ever you want.

  2. #2
    Join Date
    May 2009
    Posts
    258
    How is the database structure set up? You should be able to do this all in one query, but we'd need to know how your tables are set up and where the records are that you are trying to delete.

    Ax

  3. #3
    Join Date
    May 2009
    Posts
    104
    I'm not sure i can put all that into words, but what i figured out i think i need to do this from one query as you suggested i could. I'm not sure about how to get the results i need from one query. What i did before is made a query off of the transaction list called [qryGageTransacitonList] that showed all the records that have "CHECKED IN" in the [tblTransaction List].[TranscactionType] field. This returns 11 records. Then i made another query off of that query that showed records that matched in the fields [tblTransaction List].[GageNumber] and [qryGageTransactionList].[GageNumber]. This returned 22 records, which is the result i need to delete the appropriate records. Maybe i cant create a delete query off of a query created off of two other queries. I just need to know how to get the correct results out of one query. So again here is what i'm looking for.

    All the records from [tblGageTransactionList] that have "Checked In" in the field [GageTransactionList].[TransactionType].
    From the resulting gage number i want to find all the matching records in the field [tblGageTransactionList].[GageNumber].

    If i do this from two different queries i get the correct results but the delete query doesn't work.
    I will attach some screen shots of what this looks like, along with the SQL.
    Attached Files Attached Files

  4. #4
    Join Date
    May 2009
    Posts
    258
    The following should work:
    Code:
    DELETE FROM tblGageTransactionList
    WHERE GageNumber IN (SELECT GageNumber
    FROM [tblTransaction List]
    WHERE TransactionType LIKE '*CHECKED IN*')

  5. #5
    Join Date
    May 2009
    Posts
    104
    It didn't work. I tried some variations of the same code and no luck. If anyone has any ideas on how to return the results i need just as a normal select query i can make it into a delete query easily. That way i can test it before i delete the records.

  6. #6
    Join Date
    May 2009
    Posts
    258
    It might help if you let us know exactly what tables you are trying to get the data from. In your post before my last, you gave the following tables:
    [tblTransaction List]
    [GageTransactionList]
    [tblGageTransactionList]

    Which table has the records you are trying to delete and which table has the records that you are trying to find "CHECKED IN"?

  7. #7
    Join Date
    May 2009
    Posts
    104
    I'm only trying to pull records form one table. The table [GageTransactionList]. I want to pull records for all the gages that have been "Checked In". Every gage in the transaction list has a record for for being checked out and one for being checked in. If a gage is checked in i want to delete both of them. This seems so simple but its driving me crazy. I can get the results from making two different query's, but when i try to make a delete query to delete the records that the query returns i keep getting errors. The Word Document i attached is of the table and query's i used to get the results.
    Attached Files Attached Files

  8. #8
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    dbshaft, Not sure if either of these SQL syntax will work in Access but you might try:
    Code:
    DELETE FROM GageTransactionList
    WHERE GageNumber IN(SELECT GageNumber
                        FROM GageTransactionList
                        GROUP BY GageNumber
                        Having Count(*) > 1
                       )
    This assumes that an Item will only have 1 row if it is Checked Out but will have 2 rows once it is Checked In.

    Or:
    Code:
    DELETE FROM GageTransactionList
    WHERE GageNumber IN(SELECT TabOut.GageNumber
                        FROM GageTransactionList TabOut
                        WHERE TabOut.TransactionType = 'CHECKED OUT'
                          AND EXISTS (SELECT *
                                      FROM GageTransactionList TabIn
                                      WHERE TabIn.TransactionType = 'CHECKED IN'
                                        AND TabIn.GageNumber = TabOut.GageNumber
                                     )
                       )
    This one Selects rows that are Checked Out and uses an EXISTS sub-query to see if a Matching GageNumber has a row that is Checked In.

  9. #9
    Join Date
    May 2009
    Posts
    258
    If all you are trying to do is delete the records for gage items in GageTransactionList that are checked in, it would just be the following:
    Code:
    DELETE FROM GageTransactionList
    WHERE GageNumber IN (SELECT GageNumber
    FROM GageTransactionList
    WHERE TransactionType LIKE '*CHECKED IN*')
    Regards,

    Ax

  10. #10
    Join Date
    May 2009
    Posts
    104

    eureka

    eureka, That is what i needed. I'll test at work tomorrow. Thank you for all your help. You inspired me to post in the newbie section, All i had to do was ask the right question and you helped. Thank You, Thank you

    FYI this is the one that did the trick, although i did have to add one thing to the SQL before it would work right. For some reason it wanted the record "ID" field as well, maybe because this is the primary key.

    DELETE FROM GageTransactionList
    WHERE GageNumber IN (SELECT GageNumber
    FROM GageTransactionList
    WHERE TransactionType LIKE '*CHECKED IN*')


    In the end this is what worked, or at least what should still work when i get back to work.

    DELETE GageTransactionList.ID, GageTransactionList.[GageNumber]
    FROM GageTransactionList
    WHERE (((GageTransactionList.[GageNumber]) In (SELECT GageNumber
    FROM GageTransactionList
    WHERE TransactionType LIKE '*CHECKED IN*')));

    Again
    THANK YOU

  11. #11
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    dbshaft, Unless Access has some really strange Delete syntax, there is no column list between the DELETE and FROM keywords.

Posting Permissions

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