How to write code to delete record based on field value?

Please Login or Register  to view this content.

I have two tables: TableGroup & TableGroupDeleted

TableGroup: A group can have one or multiple administrators, or NO administrator.

Take attached two screenshots for example, in first screenshot TableGroup, I would like to delete any groups which do not have administrator. For this screenshot, I will need to need Group3, and there are two records associated with Group3; therefore, the last two records will need to be deleted. But before being deleted, those two records will need to be copied to TableGroupDeleted, which has similar data structure except last field (Copy 5 fields and add current date for last field DateDeleted).

How can I write the code?

Thanks.

you can't do it in 1 query. write 2. first one is APPEND:

https://support.microsoft.com/en-us/...8-8ef649cf3596

2nd one is a DELETE query:

https://support.microsoft.com/en-us/...0-c052cd4c3ec5

that is all you need. for your "no admin" spec, obviously you need a WHERE:

Please Login or Register  to view this content.

field = "administrator check box"
0 = not checked for that field

Can someone move my thread to Sub Forums: Access Programming / VBA / Macros ?

Thanks.

Well my apologies it's been so long since I worked with Microsoft Access or written any non-based webquaries like this. This might work with a group by clause but I'm not sure. Look that up. it is here....

https://support.microsoft.com/en-us/...a-002bb65ef3a0

Furthermore,

This is what I'm thinking....

"" Delete *from table where administrator equals false group by groupname"".

sorry I can't give you the exact answer but I'm not at my machine to run a test for you.

But now that I think about it I think writing too stacked queries is the best for you. The first one would group the results by group name and possibly return distinct records where administrator equals false. and the second one would be pulling from that first query and deleting them all where it finds the corresponding group name.