3

I want to delete a set of records as determined by a query which joins two tables. The tables contain multi-value fields (the bane of my project but I can't get rid of them at this point). I've learned that nothing is simple with multi-value fields.

If I run a query joining the tables and then delete the records by selecting and deleting them in query view, will this properly remove the multi-value field entries?

Clarification: I want to delete some records permanently from a table in my database. In order to choose the subset of records that I want to delete, I am running a query to join two tables. ( One table contains research data from which I only want to delete a subset of records. The other table is simply a list containing the primary keys of the records that I want to delete from the research table.) The "research" table contains multi-value fields and I do not believe that records containing multi-valued fields can be deleted through a delete query.

So, I wanted to view them through a select query and delete them through the resulting datasheet view by clicking on all records and selecting delete.

CS4
  • 77
  • 5
  • *"will this properly remove the multi-value field entries?"* - Remove them from where? From the list of possible values that appears when you go to create a new record or edit another existing record with that multi-valued lookup field? Please [edit] your question to clarify. – Gord Thompson Dec 15 '15 at 13:16
  • Thanks for your comment. Hopefully my edited explanation is clearer. – CS4 Dec 15 '15 at 19:50

1 Answers1

2

If you already have a table containing the Primary Key values of the rows you want to delete from the main table then all you need to do is run a query like this:

DELETE ResearchProject.*
FROM ResearchProject
WHERE (((ResearchProject.ID) In (SELECT [ID] FROM [ProjectsToDelete])));

In the Design View of the Access query designer it would look like this:

PurgeProjects.png

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Thank you kindly. Not sure why it did not work through the view but this worked perfectly, even with the multi-value fields. – CS4 Dec 23 '15 at 04:16