How to retain information from a table that is purged and imported daily.

[afr0ninja]

Hello!

I'm fairly new to access, but I'm starting to get the hang of it.
This will be a bit lengthy but hopefully it'll have enough information
for some kind soul to help me out. I'm currently working on a database
where I export data from another program, import the data into access
and then run various queries and what not to generate information
needed for business planning. I can't link directly to the existing
program, so I need to export about 10 reports that I import into
Access.

My problem is that in order to keep the data current I have to
purge my tables and then import new data to replace the old at least
once a day, sometimes twice a day.

To try to track some trends and what not, I need to save some of
the information (generally from queries) So I guess my question is...
When I import my daily information to the table, how can I take a
'snapshot' of the various queries I run and then store the data into
another table? Best ideas I've come up with so far is perhaps making a
macro that copies the data into a new table, however if I do that, then
I'm going to generate a ton of new tables which would take up a ton of
space and would probably prove to be difficult to merge back together
if I wanted to make a monthly or yearly summary type report. I've also
thought about doing the same thing with excel sheets, but again, ton of
sheets, pain to consolidate.

My final thought (and I don't know if this is possible, though
probably the easiest solution) is that when I update my reports the
main reason for purging the current data, and importing the new is
because the new reports generated will have some of the same
information as the last report. Each new report could have anywhere
from 1 to 1000 new lines of data in addition to the information
contained in the last report. Is there an easy way to import the data,
have it compare what is already existing in the table, and then append
only the new information?

Sorry for the obnoxiously long post, any suggestions or
information would be greatly appreciated.

[pietlinden]

"My problem is that in order to keep the data current I have to
purge my tables and then import new data to replace the old at least
once a day, sometimes twice a day."

What? Nonsense. You don't need to purge all the data. If you include
a datestamp in your table and update the table after importing, then
you can filter for the records you want. you're not creating enough
records to hit the 2GB limit, are you?

Importing only new data....
you can import to a temporary/holder table and then use the Find
Unmatched query wizard to find the new records in the new import. Then
you can just turn that into an append query to add them to the final
table.

And what's stopping you from opening a report with a filtered set of
records?

[afr0ninja]

pi********@hotmail.com wrote:

"My problem is that in order to keep the data current I have to
purge my tables and then import new data to replace the old at least
once a day, sometimes twice a day."

What? Nonsense. You don't need to purge all the data. If you include
a datestamp in your table and update the table after importing, then
you can filter for the records you want. you're not creating enough
records to hit the 2GB limit, are you?

Importing only new data....
you can import to a temporary/holder table and then use the Find
Unmatched query wizard to find the new records in the new import. Then
you can just turn that into an append query to add them to the final
table.

And what's stopping you from opening a report with a filtered set of
records?

Thanks for the suggestions! Maybe I'm not experienced enough with
access, but I was having a hell of a time trying to get the filters to
work the way I needed them to. I could get some of the data to show,
but not all in some cases, or it'd duplicate a few fields.. I thought
about time stamping the data that's imported, but I was afraid that
it'd start stacking up information pretty quick, and start filling the
table with unnecessary data. I'm not sure about how much the data is
as far in data size, but It's on average about 20k lines, 15 columns a
report.

I do like the idea of trying to move the data into a temporary holder
table, then use the unmatched query wizard, then append it to the
current data. I think I might give that a try.