Microsoft Access - Access 2007 delete query - Asked By D on 31-Jan-12 07:08 PM

Earn up to 20 extra points for answering this tough question.
I have created a delete query with 2 tables:  MasterNameListNeon and Payments.  I want to delete records from the MasterNameListNeon.  I have criteria in the query to get the records I want to delete in the MasterNameListNeon table.  When I view the results it shows the correct number of records to delete.  However, when I click Run I get the error message: specify the table containing the records you want to delete.  I don't understand SQL but know it is the key here.  Here is the SQL the query generated (I didn't write it and wouldn't know how), would appreciate help with amending the SQL to achieve the desired results.

DELETE MasterNameListNeon.ContactID, Payments.DonationID, MasterNameListNeon.CreateDate, MasterNameListNeon.InvalidAddress, MasterNameListNeon.LocalList, MasterNameListNeon.ContactType, MasterNameListNeon.Board, MasterNameListNeon.Volunteer, MasterNameListNeon.[Gen AP], MasterNameListNeon.[Angel Ball], MasterNameListNeon.Conference, MasterNameListNeon.Brick, MasterNameListNeon.Golf, MasterNameListNeon.Trivia, MasterNameListNeon.Walk, MasterNameListNeon.[AB Invite 2011], MasterNameListNeon.[Busch Mansion Attendee 2011], MasterNameListNeon.LossType
FROM MasterNameListNeon LEFT JOIN Payments ON MasterNameListNeon.ContactID = Payments.CID
WHERE (((Payments.DonationID) Is Null) AND ((MasterNameListNeon.CreateDate) Not Like "*2008*" And (MasterNameListNeon.CreateDate) Not Like "*2009*" And (MasterNameListNeon.CreateDate) Not Like "*2010*" And (MasterNameListNeon.CreateDate) Not Like "*2011*" And (MasterNameListNeon.CreateDate) Not Like "*2012*") AND ((MasterNameListNeon.InvalidAddress) Is Null) AND ((MasterNameListNeon.LocalList)=False) AND ((MasterNameListNeon.ContactType) Is Null) AND ((MasterNameListNeon.Board)=False) AND ((MasterNameListNeon.Volunteer)=False) AND ((MasterNameListNeon.[Gen AP])=False) AND ((MasterNameListNeon.[Angel Ball])=False) AND ((MasterNameListNeon.Conference)=False) AND ((MasterNameListNeon.Brick)=False) AND ((MasterNameListNeon.Golf)=False) AND ((MasterNameListNeon.Trivia)=False) AND ((MasterNameListNeon.Walk)=False) AND ((MasterNameListNeon.[AB Invite 2011])=False) AND ((MasterNameListNeon.[Busch Mansion Attendee 2011])=False) AND ((MasterNameListNeon.LossType) Is Null));

I have also tried a different method by isolating the ID Numbers I want to the delete from the MasterNameListNeon table and I get the same error message: specify the table containing...
Here is that SQL
DELETE [MasterNameList for Deletions].ContactID
FROM [MasterNameList for Deletions] INNER JOIN MasterNameListNeon ON [MasterNameList for Deletions].ContactID = MasterNameListNeon.ContactID;

Pat Hartman replied to D on 31-Jan-12 08:44 PM
A delete query that joins two tables, deletes rows from the many-side of the join.  It does not delete rows from the one-side.  If your criteria includes something from the many-side table, you'll need to use a subquery in the where clause to satisfy the many-side criteria.

Dates are not stored as strings unless you've done something unnatural.  The datetime data type is a double precision number with the integer portion holding the number of days since Jan 30, 1899 and the decimal portion holding the number of milliseconds since midnight.
print now()
01/31/12 8:27:16 PM
print cdbl(#01/31/12 8:27:16 PM#)
40939.8522685185

As you can see by the above snippet that I pasted from the Immediate window, there's been almost 41,000 days since Dec 30, 1899.

Since dates are not stored as strings, you can't use LIKE the way you are doing.  The criteria should be
Year(MasterNameListNeon.CreateDate) < 2008

The Year() function extracts the year from a datetime field and you seem to want to delete anything created prior to 2008.

And finally, your column names are indicative of a non-normalized schema.  In a spreadsheet you might have a column named [AB Invite 2011] but you would NEVER, NEVER, NEVER, NEVER have such a column in a relational table.  That data belongs in a separate child table that contains columns such as RecID (autonumber primary key), ContactID (foreign key to parent table), Action, ActionDate.
D replied to Pat Hartman on 31-Jan-12 10:34 PM
Appreciate your knowledge - thank you.  As for all your NEVER, NEVER, NEVERs we have to deal with the hand we are dealt.  I am having to deal with db's others have created.

Reena Jain replied to D on 01-Feb-12 02:07 AM
hi,

To resolve this error, set the query's UniqueRecords property to No by following these steps:
  1. Open the select query in Design view.
  2. If the property sheet is not already open, on the View menu, click Properties.
  3. Click an empty area in the upper half of the query window so that the property sheet displays "Query Properties" in the title bar.
  4. Set the UniqueRecords property to No.
  5. Save the query, close it, and then run the query. Note that you may now update the data in the query.
Pat Hartman replied to D on 01-Feb-12 12:58 PM
D,
I believe that was four NEVER's :)  Apparently the person who created the database never read any of my postings. You won't make the same rookie mistake though and when your manager asks you to add the same columns for 2012, what are you going to do?  Hopefully, tell him that for very little more effort, you can fix the problem so you NEVER have to add  future columns like that again.
wally eye replied to Pat Hartman on 01-Feb-12 01:13 PM
lol