Import Data from a Table into a Database that would store the data.

Hi,

I am a total novoice in access and would really appreciate your help here.

Currently a team of 100 people are updating their data in an excel on a daily basis but it is becoming a bit problematic to consolidate everything into one excel and keep it intact. Hence I needed help of access.

I need to create 2 things:

1.) One table where everyone can copy their individual data from excel and paste it in the table and click on a submit button.
2.) When clicked on the submit button the data in the table should be exported into a database.

From my little knowledge in Access I am thinking to create differnet databases for each month but if someone has a better way to do it I am all ears.....

Your help is much appreciated.. Thank you in advance.

Warm Regards

If you use Access's forms you could let your users enter data directly into the database for any new data (once you've imported existing data from within excel).
They would not have to use excel at all then. The benefit of this is that you have more control over the data formatting. Once configured correctly the form would not allow text within a number field etc. With excel you would have to first validate and possibly modify the data to meet the correct format before importing.

As for separate database per month, that seems excessive. Even a separate data table per month could be too much.
If you include a date column (or simply a Month / year column if you're not too bothered about drilling down to the finer details) then you could use queries to report on specific time periods and save those as separate months instead.

Using odbc you could display those data queries as tables in excel which you can use for charts etc.

Thanks for your suggestion AJ I am considering that as well however right now i need to take one step at a time. Currently only 2 out of 100 people have access to MS Access hence the only working solution that I can come accross is that all the 100 odd people add their daily data in the respective spreadsheets (as they are currently doing) and at the EOD either one of the 2 people just copy that data into an already created table in access and export all of that into a database.

As for your idea for creating separate data tables per month seems to be brilliant. But I have no clue how to start with it. I have tried searching the internet but it seems I cannot frame the question correctly.

I tried creating an odbc database with the following steps I found on the internet but it pops up an error stating "You cannot use ODBC to import from, export to, or link an external Microsoft Jet or ISAM database table to your database. (Error 3423)"

I am a total beginner in access and would appreciate if you could share some knowledge on how things work in access....

Sorry for the trouble

Thanks for your suggestion AJ I am considering that as well however right now i need to take one step at a time. Currently only 2 out of 100 people have access to MS Access hence the only working solution that I can come accross is that all the 100 odd people add their daily data in the respective spreadsheets (as they are currently doing) and at the EOD either one of the 2 people just copy that data into an already created table in access and export all of that into a database.

As for your idea for creating separate data tables per month seems to be brilliant. But I have no clue how to start with it. I have tried searching the internet but it seems I cannot frame the question correctly.

I tried creating an odbc database with the following steps I found on the internet but it pops up an error stating "You cannot use ODBC to import from, export to, or link an external Microsoft Jet or ISAM database table to your database. (Error 3423)"

Click Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC). The ODBC Data Source Administrator window appears.

ODBC Data Source Administrator Window

Click the System DSN tab, and click Add to create a new data source. The Create New Data Source window appears.

Create New Data Source Window

Select Microsoft Access Driver (*.mdb) and click Finish. The ODBC Microsoft Access Setup window appears.

ODBC Microsoft Access Setup Window

Enter the Data Source Name. In this example we created a data source called TestChannel.

Alternatively, you can choose an existing database by using the Select button. In addition you can access and configure other parameters, such as the Login name and Password fields, by clicking the Advanced button.

(Optional) Enter a description of your data source.
Under Database, click the Create button to create your database. The New Database window appears.

New Database Window

Enter the Database Name (e.g. TestChannel.mdb).
Choose where to save the database and click OK. A dialog window appears, confirming you have successfully created your database.
Click OK to complete the data source setup. Your newly created database appears under System Data Sources as shown below.

I am a total beginner in access and would appreciate if you could share some knowledge on how things work in access....

Sorry for the trouble