Report based on last modified date of record

  • Thread starter Thread starter mcwhirt3
  • Start date Start date
M

mcwhirt3

Hi,

I'm building a database for a grocery store. I have a database full
of deli meats and cheeses with their upc's, prices..etc. I need a way
of creating a report so that if i go into the database and change 5 of
the 500 item's prices, i can then run a report that prints only those
fives items, and none of the others who values did not change. I didn't
want to contemplate some kind of a cross-reference thing. It seems as
though I should be able to run a query against some "last modified"
metadata for each record. Any ideas? Thanks for any help.
 
I'm building a database for a grocery store. I have a database full
of deli meats and cheeses with their upc's, prices..etc. I need a way
of creating a report so that if i go into the database and change 5 of
the 500 item's prices, i can then run a report that prints only those
fives items, and none of the others who values did not change. I didn't
want to contemplate some kind of a cross-reference thing. It seems as
though I should be able to run a query against some "last modified"
metadata for each record. Any ideas? Thanks for any help.
Click to expand...


There is no such automatically maintained metadata in
tables. If you need such data, add the field to the table
and set it's value in the BeforeUpdate event of the form
where the records are added/edited.

Actually, I suggest that you do not change the price of an
item. Instead, keep a separate table of product prices.
This table would have (at least) three fields, a foreign key
to the product table, the date that the price became
effective and the price. This way you only add price
records instead of editing existing prices. The date field
might then be defaulted to =Date() so that it is filled in
automatically when the new record is created.

You also gain the benefit of having a hisoric record of
prices over time and the ability to recreate an invoice
using the price that was in effect at the time of the order.
Of course, a query to retrieve the price that is currently
in effect is a little more complicated, but that's usally a
small price to pay for the overall gain in accuaracy and
flexibility.
 
Hi Marsh,

thakns for your help...I have found the before update criteria, but how
do i script it to add the current date. My access vba is extremely
basic. I like your idea of keeping the prices in a seperate table with
a link id, then like you said i can do "back in time" invoices. The
only thing is i'm not sure if that will exactly solve my problem, since
i basically need to print a report of the items i changed during the
session i accessed the database. I have a feeling this will get me
started though if you could tell me how to script that date functioin.

Thanks again Marsh
 
thakns for your help...I have found the before update criteria, but how
do i script it to add the current date. My access vba is extremely
basic. I like your idea of keeping the prices in a seperate table with
a link id, then like you said i can do "back in time" invoices. The
only thing is i'm not sure if that will exactly solve my problem, since
i basically need to print a report of the items i changed during the
session i accessed the database. I have a feeling this will get me
started though if you could tell me how to script that date functioin.
Click to expand...


The Form's BeforeUpdate event procedure would only need the
line:
Me.nameofdatefield = Date

This way you can print a report for the days changes just by
filtering the report to the date.

If you keep a separate table prices, the you could print the
report of changes made for any date, just in case you lose
last week's copy ;-)

THe eay way to filter a report is to use a form with a print
report button and a text box where you can enter the date
you want to use to filter the report. The button's code
would look like:

DoCmd.OpenReport "nameofreport", WhereCondition:= _
"nameofdatefield = #" & Me.datetextbox & "#"

The filter text box can default to todays date by setting
its DefaultValue property to Date()
 
Back
Top