Join in WHERE Statement of a Delete Query

Discussion in 'MS Access' started by renecarol, Aug 10, 2011.

  1. renecarol

    renecarol New Member

    Aug 10, 2011
    Likes Received:
    Trophy Points:
    Has anyone been able to successfully execute a delete query that has a join in the where statement? If so, can you post an example of the code that worked? I've tried all sorts of code that just will not work. I was working on this before and was able to solve the problem by getting all the information I needed from one table. I had to revisit this issue when I was told I had to automate finding the last day of the month based on the current Year & Month (YrMo) plus the number of days based on what month it is. I created a table to include a few years worth of dates that have YrMo and LastDay (is how many days in that month). All of dates are text fields that are in YYYYMMDD format. If the information is pulled from somewhere that is an actual date field it is converted from #08/10/2011# to "20110810" or from #08/10/11# to "20110810" etc.

    I need to delete from the table CovElig the rows that have duplicate MemberIds where the End Date is not equal to "00000000" (infinite end date) and also not equal to CovElig.YrMo+MT_LastDay.LastDay (Where YrMo is a text field YYYYMM and LastDay is a column in the Master Table LastDay that has the last day of the month based on the month). So for August the concatenation of the fields would be "20110831". I also tried "&" instead of "+". Though usually "+" works when I try to concatenate.

     (SELECT CovElig.MemberID
        FROM MT_LastDay, CovElig
    WHERE CovElig.YrMo=MT_LastDay.YrMo
        GROUP BY CovElig.MemberID
        HAVING COUNT(MemberID)>1)) 
    AND ((CovElig.EndDt_Mem)<>"00000000" 
    AND (CovElig.EndDt_Mem)<>CovElig.YrMo + MT_LastDay.LastDay));
    I was able to get the code to work & do what I wanted it to with this code:
    DELETE *
    FROM CovElig
    WHERE MemberID in
        (SELECT MemberID
        FROM CovElig
        GROUP BY MemberID
        HAVING COUNT(MemberID)>1)
    AND EndDt_Mem <> "00000000" AND <> “20110630”;
    As I mentioned earlier I was told I couldn't hard code dates in I had to automate it so that it would know the number of days in the month based on the YrMo field, add that day to YrMo & compare against the EndDt_mem field. Which puts me squarely back to where I was when i try to run delete code with a join in the where clause it doesn't know what table to delete from. Eventhough it looks clear to me that DELETE * FROM CovElig (means delete the rows from CovElig). I didn't use any aliases because when code doesn't work I like to be able to clearly see what column is in what table so that I can tell better what I'm linking to what. Once I get it to work I will go back through and add in aliases for the table names.
    Last edited by a moderator: Aug 11, 2011

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice