I want to add a logged in user field in my report. I am building a log of patients screened and for compliance with our legislation I need to say that x was updated by y on said date. I have tried to add an 'update' table but I can't connect it in a query with my other data as there is no foreign key I have hospital number as my primary key in main table. In my update table I have index (autonumber) user and date.
The update table is useless without a field to signify what was updated (you've got who and when, but not what). You either need a field that can join to the main table, or some other method. Some people would add a "last updated" field in the main table and update that. If you want more history, search on "audit" and you should turn up numerous methods.
Thank you Paul you are quite right I have the who and the when but not the what! I don't particulary want to put an audit trail on this db I have them on my other dbs but as you said this may be my only option. Thank you again for your time.
if you need to know who did what and when, then in my books you have no choice.. you have to go down the audit log approach.
a simple userid & lastedit are fine, but that reflects who made the last changes to the db.... easily spoofed, easily overridden. You are only recording the last person not every person who made what changes
If this is needed for compliance checking or auditing or other quasi legal requirements then you need to track what was changed, who by, when (and ideally on what computer).
bear in mind that it is very easy to change something in a db table, there is no paper trial, its easy to fake data. If you do not have an audit trail then should any case come to court you will not be able to prove who did what.. any half decent lawyer (and I suppose there has to be one somewhere) will spot that your processes are not watertight.
if it is required for legal purposes then I suspect you may be better off using a server back end to store the data, the default data storage mechanism is all to easily compromised. It can be doen in Access, but its quite long winded, and ultimately difficult to really tie down and have confidence its secure. It needs am audit log in another db, with write and read privileges only, no update or delete
Obviously I will have to put the audit trail that I have onto this db as well but just to ask I adapted Allen Brownes audit trail for my other dbs the update table is within each db it just concerns me now whenever you say that it needs to be in a different db is that correct? I've never came across doing an audit this way. I use access 2000 without a backend server.
no it doens't need tobe in another db, Its jsut when ever Ive had the nee to do an audit log, I've found it easier to have a single audit log for any / all apps, its easier to control and regulate the persmissions, if its in another db Its a lot harder for the security permissions toge mangles or compromised.
if someone doens't have the right permissions they cannot open or read the data. in some audit apps you don't even want other users to see who did what
In my opinion, and it is only opinion, moving an audit log into another db, especially if you can hide that db from other users adds another layer of security, or obfuscation, that stops the user/developer with a bit of knowledge tinkering with the data. if its in the current db then there is always a risk that a knowledgeable user developer may blunder into the security areas, and if you run an audit log that in my view makes it no longer secure.
I understand what you mean now. It does make sense what you are saying. I assume any previous dbs with audit trail on them cannot be changed but any future dbs can be set up in this way and I take it you can link the audit db and main db in the usual way (File>link tables)