compare data in a table and look for changes

[rutica]

I'm using Access 2003.
Can I compare data in a table and look for changes depending on the date?

The primary key in my table is a combination of ProjectID and AsOfDate. My AsOfDate is always the last day of the month (ex: 1/31/08, 2/28/08, 3/31/08, etc)

Ex: my table looks like:
ProjectID ...AsOfDate .....BaselineProjectExitDate
112............7/31/08.........5/11/08
112 ...........8/31/08 ........5/11/08
112 ...........9/30/08 ........7/22/08

Could I have results show when the BaselineProjectExitDate changes from the current month (ex: 9/30/08) to the previous month (8/31/08).

So the results would show something like: ProjectID 112, 5/11/08, 7/22/08. The project changed their BaselineProjectExitDate last month and they shouldn't, so I want to be alerted when it happens. This data is reviewed monthly. I'm only interested in the current month compared to the previous month.

Thanks,

 

[nIGHTmAYOR]

well there are various approaches to that.
you can always keep a backup copy of the table
also a backup field at the same table
also an update field flag on table
also creating a security table that gets inserts from triggered after update property from where your table values are set.

now what you wanna do is up to you realy and your skills of handeling data.

 

[petehilljnr]

Here's a query you can use if you always add a record at the end of every month.
You can filter out from here to get the current month / year only.

SELECT MyTable.ProjectID, MyTable.AsOfDate, MyTable.BaslineProjectExitDate, MyTable_1.BaslineProjectExitDate
FROM MyTable INNER JOIN MyTable AS MyTable_1 ON MyTable.ProjectID = MyTable_1.ProjectID
WHERE (((Month([MyTable_1].[AsOfDate]))=IIf(Month([MyTable].[AsOfDate])=1,12,Month([mytable].[asofdate])-1)) AND ((Year([MyTable_1].[AsOfDate]))=IIf(Month([MyTable].[AsOfDate])=1,Year([mytable].[asofdate])-1,Year([mytable].[asofdate]))));

It can give you some ideas at least.

Regards,
Pete