Delete Records in multiple tables based on specific criteria

[sbneale3450]

Hello, I am building a database where there are multiple different types of warehousing data in different formats brought into different tables. the only common item in each record is the week number. Example of date types are Lines Picked, Cases shipped, Errors reported, etc. I have everything working so far, but I am trying to come up with a way to clear the finalized date quickly in case there are errors that need to be fixed prior to reporting out. since there are really no relationships I am struggling to find an quick solution rather than build a query for each of the 10 different tables and creating a macro that runs them all. what would be ideal is an input box that pops up requiring a week number entered and will remove the records from all the tables based on the data in the input box. I am fairly new to Access and some of what I find on google is confusing. I appreciate any help you can give

Steve

 

[xenou]

Hi,
You will have to write 10 delete queries so probably your original idea was okay.
The only way you could do it "at once" would be with a cascading delete that would require relationships between all the tables.

Strictly speaking deleting data is dangerous. More commonly you have a column for IsCancelled or IsInactive and just set a flag or bit value (true/false or 0/1 or 0/-1). It somewhat depends on why you are getting these "errors" - you may find it easier to just delete but if so be forewarned that you can't undelete.

Ten tables is also a lot if this unrelated data is in fact in some way related. Perhaps you don't need so many tables.

 

[sbneale3450]

the reason for so many tables is that there are 2 different warehouse management systems for different part brands. they report out in completely different formats. I have a total of 25 tables, most of which are temporary so when I import the data, i can manipulate it into the same format. They all come together on the final 10 tables that i referenced in the prior post. When i said errors, I meant errors in the data itself, or human error when importing data, not access errors. (This database is being used by other people that are not familiar with access at all, and import the wrong data from time to time) All the raw data is in spreadsheets that are imported so deletion of the records is not so concerning as there is a backup stored elsewhere. I appreciate your assistance and will get started writing all the delete queries.

Thanks again

 

[xenou]

Okay. There should be a way to provide a single parameter to the queries so that even though you run ten of them, the user only has to input the value used in the deletions only one time. You might be on that already if you are writing a macro for this.

 

[sbneale3450]

It ended up being only 8 queries that had to be written. this is the code I used to run it all

Private Sub Command2_Click()
Dim LResponse As Integer

LResponse = MsgBox("Are you sure you want to continue? this cannot be undone!", vbYesNo, "continue")


If LResponse = vbYes Then
Dim MyPassword As String
MyPassword = "zebra"
If InputBox("Please enter password to continue.", "Enter Password") <> MyPassword Then
Exit Sub
End If
DoCmd.OpenQuery "delAllErrors", acViewNormal, acEdit
DoCmd.OpenQuery "delAllPicks", acViewNormal, acEdit
DoCmd.OpenQuery "delAllShip", acViewNormal, acEdit
DoCmd.OpenQuery "delDealer2Dealer", acViewNormal, acEdit
DoCmd.OpenQuery "delIBXDock2", acViewNormal, acEdit
DoCmd.OpenQuery "delOBXDock", acViewNormal, acEdit
DoCmd.OpenQuery "putVErrorsBack", acViewNormal, acEdit
DoCmd.OpenQuery "delVAllErrors", acViewNormal, acEdit


Else
MsgBox "Cancelled", vbOKOnly, "Cancelled"
Exit Sub
End If
MsgBox "Complete", vbOKOnly, "Complete"
DoCmd.Close acForm, "CLEAR DATA", acSaveNo


End Sub


It seems to work well enough for what I need