Join in WHERE Statement of a Delete Query
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.
|All times are GMT +5.5. The time now is 09:40.|