Delete Query Vs Tagging in MS Access

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScManaging Partner
CERTIFIED EXPERT
Christopher Hankwembo is a Zambian Chartered Accountant and also developer of CA Premier Accounting Package
Published:
Updated:
Edited by: Andrew Leniart
If your application will be used by financial auditors, then never ever use the delete functions to remove incorrect documents from your ledgers because financial auditors will require to audit, even cancel or reject documents and reasons for their rejection.

Introduction


When we develop our applications in Microsoft Access we always take into account the intended objectives of the app. Mainly we want to ensure that the business integrity is not violated at all. Now taking into account the business ethics and requirements of financial auditors, we may consider dropping the idea of using the Ms Access Delete action queries to delete incorrect transactions, so we should consider using tagging methods instead. 


The danger with this kind of queries is that it does not give an audit trail for the financial auditors to follow. For example, once a document is deleted from the tables as long as a printed copy is not reserved, then that is it, it can never be recovered, then we have to face the auditors about the missing documents since our sequence will have some gaps.


Discussion


Instead of using this delete query, the best method is to avoid it altogether and simply use the tagging method.


We use the tagging method by simply creating a control in those tables which we want to filter the affected transactions, for example in a sales invoice table, we can add a control called rejected invoices. We can update our rejected invoices control by using an update query either through the built-in update Ms Access queries or VBA code with some criteria. 


Once the update query is done and working, then to filter the rejected invoices we simply reference that control in our query. For example, if our update query is pasting a “2” in a table control called tblsalesinvoice.reject, then at query level we can quarantine those invoices by adding this <>”2” in the criteria row of a query under that control.


Below is a simple update query:


UPDATE tblsalesinvoice

SET tblsalesinvoice.reject = 2

WHERE tblsalesinvoice.ID = '1';


As long as the customer report is bonded to the filtered query like the one described above, I can guarantee the readers that the rejected invoices will never ever be part of the report, but can be printed for audit purposes.


If you want you can use a form with a combo to help you select the required criteria. But you have to run this query from a form, however, to do that you have to create a click button and reference the query update name. It’s also possible to set off and on the query warning messages or replace them with your own if you so wish.


Why is the tagging method better than delete function?


Below are the reasons:


  1. A proper audit trail is kept for the financial auditors and other managers
  2. The integrity of the system will also be appreciated by all
  3. It reduces the chances of fraud
  4. Easy to generate exceptional reports for those rejected transactions for scrutiny



0
707 Views
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScManaging Partner
CERTIFIED EXPERT
Christopher Hankwembo is a Zambian Chartered Accountant and also developer of CA Premier Accounting Package

Comments (1)

CERTIFIED EXPERT
Distinguished Expert 2020

Commented:
It depends on local regulatory, financial data could be kept for at least N years before it can be purged. financial auditors will audit on the what, where, when, which, how of your data is being stored, modified, deleted.

the Confidentiality, Integrity, Availability (so called CIA) should always be taken into consideration for the information system that involving data.

fraud could also happen if the data integrity was compromised regardless it's whether delete query or flag tagging.

and it not only applied to MS Access but in general applied to other DBMS/ RDBMS as well.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community