Report Of Modified, Deleted, And New Records Over The Past Month, Access 2010

[ndthl]

I am using Access 2010 on Windows 7, although some users of the DB will be using Windows XP.

I want to make a monthly listing added, deleted and modified records over the past month. We have a database of urban poor community locations (in one table) and various other tables of demographic data collected at those locations (such as eviction information, eviction threat information, relocation site survey information). We have staff who regularly visit these urban poor communities and will be updating the database throughout each month, such as by adding new records about eviction threats, or modifying existing records based on new information from community members. Occasionally, records will be deleted from some tables, for example when a community is evicted (in this case a record will be removed from the urban poor community profiles table and another added to the evictions table).

We want to generate a monthly report of these transactions. It will be fine, at this stage, for the report simply to list the entirety of each record for which there has been a modification, or where one has been added or deleted. From what I have read here:

http://www.accessmonster.com/Uwe/For...ecord-modified

a relevant response being:

"A trigger is the way to go. This works very well for me.

Use something like this in an update trigger.

IF UPDATE(NPLastUpdateby) OR UPDATE(NPLastUpdateDate) RETURN

--Update the table to show who the last update was made by and when to show
on the form
UPDATE p
SET NPLastUpdateBy=@strCurrentUser, NPLastUpdateDate=GETDATE()
FROM NP_Products p JOIN inserted i ON p.NPProductID=i.NPProductID

...we will need to update our data entry form such that every record has a date stamp, and that this date stamp is updated every time a record is modified. Hence we can construct a query which projects all records satisfying a date range (i.e. the past month). The only problem is returning deleted records... is there a way to retrieve these via a query?

Wil

[ndthl]

This also looks useful:

http://www.accessmonster.com/Uwe/Forum.aspx/access-formscoding/26059/Last-modifi
ed-Record#RateAnchor

Here they suggest:

Do you have a date field in the table for storing the date and time of
when
the record was last modified? If not, then you are probably out of luck. I
always put the following fields in tables that I need to track:

Created_By
Last_Updated_By
Creation_Date
Last_Update_Date

But we need to find a way for the database to automatically update these 4
fields.

Then we can make a query using the Last_update_date field to get the ones
that have been updated in the past month.