Import data from Excel monthly - not manual input all data

[yanchen0815]

Hello everyone.
This is my first time posting thread here.

I was assigned by my manager to design an Access database system that is able to import all data from excel file monthly and creating charts & tables to analysis how each sales people and industry perform.

We originally have a big excel master sheet that has more than 10 sheets. I tried to import the current excel into access, but then i realized that this is not gonna work. because for next month, there will be new data and I can't do the whole import process over and over. Plus, after this system is designed, the users will be someone who has no knowledge in access, so i need to create a user-friendly system for them to use.

My questions is:
since the data is always cumulative number, if I imported current excel file into access, when the next month comes, how to update the new data into excel. p.s. EXP. Mike's sale volume is different each month, and with the access system, for that column, it will be a cumulative number, like the total from the month of November to this month. how do i achieve this kind of update/import goal?
I tried to link the excel to access, but by doing that, I will not be able to set relationship or change the attributes of any data type in access.

Sorry for my bad English, if anything is not clear, please let me know.

Thank you in advance for all of yours kindness help.



Regards

[June7]

Since the spreadsheet is cumulative, the easiest may be to delete all the records from Access table and re-import everything each month. This can be automated with code behind a button on form.

Why have Excel involved? Could do the data entry/edit directly in Access.

[yanchen0815]

Thank you so much for your reply.
My goal is to overwrite the existing data.
We stored everything in an excel file, and all data in excel updated on monthly base.
I can't manually input all data into access, because there are way too many of them.

As you stated below, I am new to Access, would you mind to tell me how to set up the button like the one you described?
"the easiest may be to delete all the records from Access table and re-import everything each month. This can be automated with code behind a button on form."

Again, thank you so much for your kindness help

[June7]

How is inputting data in Access more difficult than inputting in Excel? Import existing data once and then do future entry/edit with Access.

Deleting all records is simple. Can use an Access query object and run that query from macro or VBA code. Or just VBA code that executes an SQL action statement:

CurrenDb.Execute "DELETE FROM tablename"

Now the process to import can get complicated. Options:

1. the import wizard - it can be invoked with code

2. use saved import specification - explore DoCmd.RunSavedImportExport

3. explore DoCmd.TransferSpreadsheet

4. open and manipulate Excel objects in VBA

Common topic. Can find more info in forum or web search. Start with http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm