<

Delete Query Vs Tagging in MS Access

Published on
3,166 Points
166 Views
Last Modified:
Editors:
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Christopher Hankwembo is a Zambian Chartered Accountant and also developer of CA Premier Accounting Package
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
1 Comment
LVL 57

Expert Comment

by:Ryan Chong
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.
0

Featured Post

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month