How to monitor updates?

[Golfinray]

I have several tables that are updated by forms. We have four managers who
add data dailey by using forms. The forms are updating tables. I need a
report of what updates were made each week. Each manager has their own forms
and tables, so I need to do this 4 times. Then there is one form and table
that all four managers use so I need to know that it was updated also. I
don't need dates because I don't need to know when they updated, whether it
was Tuesday or whatever. I just need to know WHAT was updated. For example,
one field is Agreement form. If they added data to that field last week, I
need to know it, and how many times. All help greatly appreciated. Thanks!!!

 

[Evi]

If you need a running report of when a table was updated and what it was
updated to, then you need to store that data in a seperate table. The
table's fields
can have the same name as the fields you wish to track apart from one field
which has the Default Value Date() and is a date field. Whenever the
fields you wish to track ares updated, you can use the AfterUpdate event of
that field to run an append query and put an entry into the table.

It can append the value of that control as it is after its update or just a
tally of 1 which can be summed in your report.
Filter the date field
Between (Date() -7) AND Date() for the last 7 day's data.

Are you OK with coding that?

Evi

I have several tables that are updated by forms. We have four managers who
add data dailey by using forms. The forms are updating tables. I need a
report of what updates were made each week. Each manager has their own forms
and tables, so I need to do this 4 times. Then there is one form and table
that all four managers use so I need to know that it was updated also. I
don't need dates because I don't need to know when they updated, whether it
was Tuesday or whatever. I just need to know WHAT was updated. For example,
one field is Agreement form. If they added data to that field last week, I
need to know it, and how many times. All help greatly appreciated.

Thanks!!!

 

[Golfinray]

I can build the filter ok, but how do I add in a date field to my tables
with, say, dates from 1/1/2008 to today? Will the date field then have to be
updated or will it automatically add tomorrows date and so on? Thanks so much
for the help!!!!

 

[Jeff Boyce]

I can't be sure from your description, but it sounds like you might have
"one table (and form) per manager". If so, then this describes ... a
spreadsheet! Unless each manager provides completely different data, why
not use a single table with one additional field for the manager's ID?

Or am I missing something?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Golfinray]

No Jeff, each manager has unique data. I wish I could just throw them all in
one big table and query but I can't do that. Different things are tracked by
different managers. Thanks.

 

[mscertified]

You can use the built-in functions either date() for just the date, or now()
for the date and time.

-Dorian

 

[Evi]

In the Table's Design View, next to Default Value for the field, type Date()
and whenever anything is added to the table, that day's date will
automatically stored. I use this alot (usually with the default value as
Now() and formatted as Date and Time) so I can keep track of when records
have been added.

It sounds as if , for some reason, you want to fill the table with
historical data, Golfinray, rather than have it keep track of updates from
this point forward. If that is so, then you could add a chunk of dates to
that field in your table using an append query and using a cool idea by Dale
Fye.

Add a table to your database called TblNumbers. It has only one field called
intNumber
In that field, type in the numbers 0 to 9

Create a new query. In Sql view paste in the following 2 lines (if the email
breaks up, the second line starts with FROM)

SELECT [hundreds].[intNumber]*100+[Tens].[intNumber]*10+[Ones].[intNumber]
AS intNumber
FROM TblNumbers AS Hundreds, TblNumbers AS Tens, TblNumbers AS Ones;


Your qry will have 1000 records Save the query as QryNumbers.

In a second query, add this query and put intNumber into the grid. In the
next field type

AllDates: #01/01/2008#+[intNumber]

In the criteria row under AllDates type

<=Date()
Check your data then
Use the Query Type button to change this into an Append Query and append
these dates to your table. Remember, there is no need to do this for future
records because your append query will automatically fill in this field with
the current date.

Evi

 

[Dale Fye]

Sounds like you need a Manager_Things table ;-)

--


email address is invalid
Please reply to newsgroup only.