Help me understand the Import and Up-to-date From Excel

[Myproblem]

folks, I am trying to figure out how to get data from many excel files via access. I know how to use access (let say basic level, etc. import, export excel files, primary keys issues... on Excel side I know how to make or modify some VBA codes)
But, my problem is as follow:
I have each month huge, big excel file about sales data (arround 60-70 thsd rows, and about 120 columns).
When I tried to make sales history by region, sales person, product etc. I figure out that I need Acces to import data from named Excel workbook, but it is for one month only.
My problem getting being bigger, when I try to make sales history for about at least 26 months before. I tried to use Access 2007, to import one month by month in different tables, but I do not what to do next - how to make one big Access data table, which contain all 26 months of sales history in just one Access table or something like that. Access 2003 or 2007 is not problem for me, I have both.

The second big issue is how to Up-to-date data from these excel files. I have no idea, or I am just stuck in the middle, no break-point for me.

Help me please,

 

[xenou]

When I tried to make sales history by region, sales person, product etc. I figure out that I need Acces to import data from named Excel workbook, but it is for one month only.
My problem getting being bigger, when I try to make sales history for about at least 26 months before

The data would normally be stored in one table. You will add a date field, or year/month field(s), to keep track of the different "layers" of data that are imported. Personally I make a habit of adding a "DateCreated" field also, so that if needed I can identify data by when it was added (if a mistake occurs).

Keeping data up to date I'm not sure about. Sometimes you have to just "reload" data for a certain period, but it depends on where the data is coming from and why it is changing.

 

[Myproblem]

that is my problem, to get one, just one table. I am stuck at point when I imported each month by month. My column heading have all requared field to be filtered and used by queries or make some report.
I excually have no idea how to, let say merge all monthly tables into the just one, call it Master one table.

Up-to-date problem is about changing some details in excel files workbooks after that workbooks was imported into Access - how to resolve that problem.

Any idea how to make Master data base, and overcome Up-to-date problem?

 

[xenou]

The master table has the same fields, with only one extra - a field that identifies the month of the data (or, the workbook it came from, or some other such identifier).

For keeping data up to date you must reload the changed data.

 

[Myproblem]

all the fields are the same, have same headings (and date of month two)

over weekend I tried to resolve problem using append query table, but my two table at which I tested appen query table, are so huge that only run query last forever (which is not OK).
I guess I should first create queries for each month, to get less data - "lower" table, to get it more operate for appen query data table.

my question after this is: can I create query (and after that) based on another database table, not from table in my current database
I tried but failed.

I think this is solution (for now for me)

thx xenou

 

[james_lankford]

this doesn't have the DateCreated field like xenou suggested
I agree that would be the way to do it
but for simplicity's sake I'm going to leave that out (also becasue its really, really late (or really, really early depending how you look at it))

import each excel workshhet into its own table

something like
your_month_table_2011_01
your_month_table_2011_02
your_month_table_2011_03

fix up any of the data in those tables: dates, times, whatever you thnk is necessary

then do this

select 
    * 
into 
    a_new_table
from 
    one_of_your_month_tables 
where 
    1 = 0

that creates a new table named a_new_table
that new table contains no rows
it is an empty table with the exact same structure as one of your monthly tables


then do this
this will actually insert the rows into the newly created table
and it should happen really fast, pretty much instantly

insert into 
    a_new_table
select 
    *
from 
    your_month_table_2011_01

then just do it over and over for each month
it will append the next month's rowns into the same newly created master table

insert into 
    a_new_table
select 
    *
from 
    your_month_table_2011_02

yes, its repititious and poor design running the same insert query over and over (26 times in your case)
but the solution is fast, it inserts really quickly
and it may fit your needs


and lastly,
you don't update the data in the excel files

you put all the data into access and then you run select queries and have access sum and calculate the sales history for you
then you take the results of that query and put it inot a brand new excel workbook and make your pivot and charts

 

[Myproblem]

thx james,
I will try it, after I made some new tables in Excel with less data, rows to get into Access

I was stupid to think that around 60 thsd and each month more rows can be easly working in Access, multiply it with at least 26 monthly history