tracking changes

[Guest]

I am currently creating a database to track sales information. I use it to
create numerous weekly reports. I am trying to find a way to track the
changes that occur from week to week. More specifically, if a change occurs,
I would like the field for that record only to bold, and defalt back to
regular font the following week. I know a little bit about writing code but
not enough for this. There's got to be some way to do it, can anybody point
me in the right direction?--

yque

 

[sebt]

This should work as long as you're not trying to track changes to too
many tables - lots of tables and it'll be a bit unwieldy.

Take a copy of your database every week, and rename to e.g.
LastWeeksData.mdb.
Attach all the tables (that you want to track) from LastWeeksData.mdb
into the "live", original copy of the database. (you'll have to rename
the attached tables - for a table "Sales", rename it to e.g.
"SalesLastWeek").

Then you can find out what's changed by joining the current table to
last week's table, on a unique key column - e.g. RowID:

SELECT tw.RowID,tw.COlumn1,tw.Column 2..... etc,lw.Column1,
lw.Column2.... etc
FROM
Sales tw
INNER JOIN
SalesLastWeek lw
ON tw.RowID=lw.RowID

Using this set, you can look for instances where a particular column
has changed:
WHERE tw.Column1<>lw.Column1 (watch out for NULLs: NULL<>[any value] is
never True)
or where ANY column has changed:
WHERE (tw.COlumn1<>lw.Column) OR (tw.COlumn2<>lw.Column2) OR.... etc

Every week you'd have to make a copy of the database, delete the old
LastWeeksData.mdb and rename the copy to LastWeeksData.mdb.

cheers


Seb

 

[Ron2005]

Think of what you are really saying.

If you have 20 fields that you want to monitor in this way then you
need:
either
1) an extra field for every one of those fields to tell you that they
were changed or when they were changed. And
A.) if you just say that it was changed then you have to weekly
run something to set all those indicators off.
B.) if you use a date for each the logic has to decide of each
field was it was it more than 7 days ago or save the week of the
change and see if it was this week or not.

OR
2) A log file
A.) containing the unique sale ID , a field ID, and the same
either date of week number of change
B.) The query for the report would be really, really overpowering
trying to find all the matching information.

OR

3) A log file containing a copy of the whole record BEFORE and the
whole Record AFTER and then figuring out how to create some special
reports comparing the two. The trickiest part will be figuring out how
to logically and "Clearly" presenting the information. (This is
especially true if the field changed values multiple times during the
time period.

A simple change log, showing what sales ID, what field, old value, new
value, when, and by whom (created on the AfterUpdate event for each of
those fields.and
then a report listing off the log might be a whole lot more resource
friendly.

I have met some auditor types that seemed to like these types of
reports, but then never look at them and a change log usually gave them
all the information they needed.

If someone else has seen a nifty / resource friendly way of doing all
this, it would be interesting to see.

Ron

 

[Guest]

What I have done for fields containing dates is set conditional formatting
which automatically bold or un-bold depending on whether or not the date
falls within the date the report is printed and 5 days previous. You
mentioned setting a date modified column for each field. I know how to
create one that shows when any portion of the record is modified, but I don't
know how to set the date modified to relate only to a single field in the
table. Could you help me with that?

 

[Ron2005]

The coditional formating you mention would show when the value of the
date was relative to this week etc. but not when the value was changed
or set. - as you propably realize.

Now to the rest of it......
If you want to monitor when a field changed by this method then you
would need to:

1) For each field that you wanted to monitor create a second date field
that would be part of the table but not "show" on the form but was
included in the query. ex: field1changedate for field1
2) in the onupdate event of that field (not the form but the field
itself) load it with today's date: ex: me.field1changedate = date().
3) in the condtional formating for the field itself use the expression
option and the test would not refer to the field itself but the change
date for that field. ex.
on conditional formating for field1
expression [field1changedate] > (date() - 7)
then add color and/or bolding, etc.
The example would show anything changed in the last 7 days
If you allow null values in a field then remember that bolding will not
show the change since there is not value to bold. You may want to add
some background color change also.

 

[Guest]

Thanks for your help so far, and for your patience.
I understand the first and third part, but I'm not sure where exactly to put
the expression ....fieldwhateverchangedate = Date( ) ... would I put that on
the form, query, or table? And if it's on the form, do I put it in the Before
Update property of the new field or the old one?

 

[Ron2005]

On the form, when sitting on the individual fields that have the
changedatefields. It can be before or after, but I tend to put those
types of things AFTER the update. Just psychologically more pleasing to
me.

 

[Guest]

Ok, I got it to work. However, I did end up having to write a macro to put
in the after update section to make it autodate the change date field.
Thanks again for all your help.