How to Report Delta on Queried Data?

[Darrell]

I am Access challenged so I thought a few questions before diving back into
the application might speed my delivery of a requested report. I have a SQL
database which contains a data field that should be updated every day. I
think - I want to import this data into an Access database to create a new
table each day so that I can then compare the records in two tables against
each other for a delta in one of the fields and report on any records that do
not show a delta .i.e not data was updated!

Am I on the right track? Can I create a SQL query to create a new table each
day? Can I create an Access query to then produce the delta report I need
each day?

Darrell

 

[Darrell]

If I import data each day could I then compare records by date verus a new
table each day? Still trying to get my mind around the best design...

 

[Jeff Boyce]

Darrell

I'm having trouble wrapping my mind around what it is that may have been
updated between one day and the next.

I will suggest, strongly!, that you do not want to create a new table for
each day's download. If there's no difference in the data
categories/fields, only (potentially) in the values in those fields, and if
each record has a date/time (stamp) field, put all records in one table to
start with.

Note, however, that raw import data may not be well-normalized. Even though
you can put all import data in one table, you might find that you get better
use of Access' relationally-oriented features/functions if you use queries
to 'parse' the raw import data into a more normalized (i.e., "permanent")
table structure in Access.

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Darrell]

Jeff,

Thx for the reply. After thinking it over I am trying to set it up so that I
have just one table and I am hoping I can compare records by the date they
were created? i.e. I can compare all records created today against all
records created yesterday? So, each day when I run the query against the
source DB I get a new set of records created by date, and then use that date
for my comparison?

I am comparing the "Actual Work" field from MSPS 2007's Reporting Database.
We report progress on project task assignments everyday, therefore the data
should change every day "IF" resource's are updating their progress
"Everyday" as mandeted by management. Who now wants a report showing who is
not updating everyday as directed. Thus my need to create this report. So I
am trying to pull all tasks in-progress for all projects across the
organization and produce a list of tasks that have not been updated (no
change in the Actual Work field) by Project Name, Resource Name and Task Name.

I can muddle my way through this and eventually get it done I was just
hoping for some advise on the best approach.

Thx again for any assistance,
Darrell