create an application to copy tables

[Guest]

Hello , I am trying to build an application where I have a weekly spreadsheet
of data that is averaged and sorted prior to me getting it. the data in the
spreadsheet is 42 thousand rows each week and a dozen or so fields , the key
is the field Element as this is what all the data is relevent to . I have to
due comparisons each week and create averages and reconcile which elements
have been removed or added and break them out by region, so for example
f1=region f2 = site f3 = state f4 = element f5 = usage, currently I load
each week into a seperate table and create querys to gather data and match
each element to its region by a seperate table and this adds my region to the
element. I was trying to figure out how i could create a temp table where i
load teh spreadsheet into and the previos weeks table will shift over so i am
only using 6 weeks worth of data at a time. the last table would simply be
archived . By doing this I was hoping that i could leave all my querys the
same and have them reference each table as week 1 week 2 etc. and within my
import i would add the date to a seperate field as oppsed to naming my table
by date which is how i currently run my querys. any help or guidance would be
greatly appreciated.
Dave

 

[Larry Daugherty]

Absolutely, positively do NOT create a separate table for each week or
other time period. All of the data you are importing belongs in the
same table with a date/time field for its applicable week date. Every
line of data imported at the same time would share that date value.
Yes, at 42K records per week the gable will grow. That's what tables
do...

With all of your relevant data in that single table, all of the data
you need for your reports is available in a single place. You don't
need a temporary table.

HTH