Counting # Of days on a report

[Tiffany]

I reciieve an e-mail daily that is an excel spreadsheet. I have a linked
table that updates my table in access then I use an append query to append
any new data. What I need to know is if there is anyway I can caculate the #
of days a record is on that report and the date it drops off. Can someone
please help me figure this out. Thank you!

 

[Dale Fye]

Are you storing the date it was added to the report in your database? How
about the date it is not in your report? If you are not storing those two
dates, or the date it showed up and a value indicating the number of days
before it was dropped from the report, then I don't know how to help you.

What I would do is:

1. When you append new data, append the current date to the DateAdded
field(you can set the fields default value to =Date() to accomplish this).

2. Then, after you do the append, I would do an unmatched query to identify
records that are in your access table but are not in the linked table, where
the DateDropped field is NULL. Then I would use this query to update the
DateDropped field for each of these records. Something like:

UPDATE tbl_yourTable
LEFT JOIN yourLinkedTable
ON tbl_yourTable.[PkFieldName] = yourLinkedTable.[PkFieldName]
SET [DateDropped] = Date()
WHERE yourLinkedTAble.[PkFieldName] IS NULL

Then, once you have these two dates filled in, you can compute the # of days
the record was on the report.

HTH
Dale