Audit Trail from Form changes when form source is query

[OTOTO]

Hello,


I have a form which pulls data from 2 tables and uses a query to do so. Updating the table will update either/both of the tables.

What I'd like is an audit table to keep track of any and all changes/additions that take place in the form.
I've found an example of an audit trail when a form uses only one table and is only updating one table. it references the table's primary key in the audit table. I have 2 separate primary keys, both are autonumber fields, for each table. Should I add a primary key to the table generated by the query? How would I do that?



Any suggestions on audit route?

Here's the source of the audit sample I'm going off:
http://www.rogersaccesslibrary.com/f..._topic399.html

[ItsMe]

What is the purpose of this audit trail? What data will be stored in the trail?

Why not just store data in the existing table(s) and use the existing tables for your audits?

[OTOTO]

What is the purpose of this audit trail? What data will be stored in the trail?

Why not just store data in the existing table(s) and use the existing tables for your audits?

I am making a database for our company record retention schedule. Therr were two tables in excel that were imported to access. One consists of business departments, the records they create and the schedule code that applies to them along with the rules regarding the number of years they need to be kept. The other table contains just the record codes and examples of the types oc documents that specifically apply to those codes, this is to help people figure out how to apply codes to new documents that they create. retention codes to be applied.

Various changes can be made over time and noted in the database, periodically we will send the update list to the company that stores the documents for us so they can research, be aware, and confirm changes. A table which tracked data changes was requested.

Using the link in my first post i am able to create a log of changes. My layout is different though, not using a subform rather just 1 form generated from a query to aggregate the info. I have not finished the code for the audit table yet. Any suggestions? I am a n00b

[OTOTO]

The data will be record primary key(i havent worked this one out yet as the query can reference the primary key of one table but has no key of its own), field, username, time, old value, new value. How would i be able to see old values in the existing tables? Possible?

[ItsMe]

I did not download Roger's example. However, an audit trail for a relational database, a trail of changes made to the tables within the DB that can audited, is different from keeping track of operations of a business. Access is a tool to manage data. So build a typical Relational Database to mange the different department's Records and Schedules. Create tables for Departments, Codes, and Accounts.

The Accounts table may be enough to track events/activities of operations. A new account is created, add a new record to your events table (tblAccounts). I may be oversimplifying things but you need to understand some basics so you do not spend time aimlessly wandering around.

I suggest you understand what an RDBMS is before you even start to click things inside of your Access app.
https://www.accessforums.net/databas...tml#post217601

[OTOTO]

Hi ItsMe,

Not the reply I was expecting, but valuable nonetheless as it may get me closer to getting this correct by reviewing the basics.
Reviewing the Donald Ravey RDBMS Principles PDF I've already noticed that I have a primary key chosen (similar to SS#s) which may cause headaches in the future.

I will restart with a more concerted brainstorming BEFORE getting caught up with the tool.

-S

[ItsMe]

Unfortunately, you need to learn how to walk before you can run. If you are really anxious to create a DB, get the fundamentals of Relationships down pat and how to create JOINS in queries. Concentrate on the first three forms of Normalization to get your table structure correct.

[OTOTO]

Rethinking the layout, I may be getting stumped by my excel-expectations. I have combined the two tables in excel into 1 table with 33 columns. I am thinking of each row as a very unique record and I am not intuiting the breakdown of entities and attributes.

Access Wizardry wants me to create tables for all repetitious data. I see value in this for reaching 3NF but I don't see why it is necessary. May there be situations when 2NF is enough?

Following the 3NF Normalization I will have at least 2 tables that contain only 2 columns, some data and a primary key. Is my proclivity to limit the number of tables in the database without merit?

[ItsMe]

...I will have at least 2 tables that contain only 2 columns, some data and a primary key..

This sounds like progress. Most of my databases have several if not many tables like this. An example of such a table would be tblStates, a table that has a Primary Key (Long Integer) and a description of the state (Text).

One benefit of having tables like these is when you, the developer, are managing Constraints. You do not want a user typing/inputting "Texas" one day, "TX" another day, and "Texus" on another. Store the PK value of "Texas" in the FK field of the relative table. There are other benefits to storing Number types rather than Literal Text too.


Do you really need to follow the first three forms? It depends. I say if it does not work on a flat spreadsheet, follow the first three forms. The benefits are not apparent until after you use the final application and start adding functionality to your application. Applications are rarely ever "finished". Poor foundations cause issues down the road when trying to add functionality and or tie data to other, outside, data. In the end, it is just easier to enforce referential integrity and data constraints.

[OTOTO]

See: https://www.accessforums.net/databas...tml#post234952

I re-posted this as a new question for the design forum