Looking For Best Practice: Reoccuring data import and normalization

[milkman2500]

Hi,

I work with an employee database system that provides very generic reports. I would like to take 1 giant employee report and break it down into multiple tables to be imported into Access. I did this manually once to see if it was possible, and I pretty much used Excel's "remove duplicates" feature to create the tables and imported each table. I then established the relationships in access and created queries and reports. Now that it's been a month, I need to refresh the data in my database by doing the same process again, however I'd like to automate it if possible as I'll need to do this every month.

I was thinking of creating macros in excel to split the tables, then create a saved import in access.

Do you think this is the best way to go, or can you recommend an alternative?

 

[jdraw]

If what you are describing is a database application, why is it in Excel? Excel may be the best tool to for it. Do you have Access databases? Do you need both software packages?
You can link Excel and Access, if it makes business sense.

Need more info on WHAT your employee database entails.

 

[milkman2500]

Thanks for the replies.

Perhaps I should clarify more:

Most access tutorials/documentation I find focuses on designing an access database shell without data. The data would be input into the system by a user through forms, and reports could be pulled by other users after data has been inputted.

I'm attempting to build a database where I have all of the information in excel files, however I want to normalize the data in tables on a monthly basis.

For example:

I can download a spreadsheet from a database; let's say it looks like the following:

Employee ID
Employee Name
Country
City
Job Title
Job Family

As you can see, this table is not normalized. I want to be able to take this excel spreadsheet, and break it down into tables in Access.

Ideally, I would have the following tables:

Employee ID
Employee Name
Country_ID
City_ID
Job_ID

Country_ID
Country_Name

City_ID
City_Name
Country_ID

Job_ID
Job_Title
Job_Family_ID

Job_Family_ID
Job_Family

The first time I completed the above process, I setup reports that reference the above fields based on October data I normalized from the available spreadsheets. It is now November, and I would like to update my database with November data and have the reports print based on November data.

How would you recommend I automate the process of converting an unnormalized excel spreadsheet into a normalized access database?

 

[jdraw]

Create Normalized Tables that you want to work with in Access.
Bring your excel data into a "Holding" Table in Access.
Build some queries (and possibly some validation routines) in Access that are used to move the appropriate data from the Holding table to specific tables in your Normalized table structures.
If you need specific queries for analysis, build those to work from your Normalized tables.
Build your Forms and Reports in Access to work from your Normalized Tables.

When you bring a new month's data from excel - it goes to the Holding Table.
You can set up your queries(may be several of these) to move the data from the Holding table to your normalized tables as a group with a small vba routine.

After the data is loaded into your tables you could backup that table, or the excel file for that month (in case you have to reload/recover from something).

 

[John Big Booty]

The sample posted here, is based on something I use on a daily basis and I import data from an excel spreadsheet on a weekly basis. It's should give you some pointers on how this task can be achieved even though it's quiet different to what you are doing.

You will notice that the code used to import the excel data first imports the data into a temporary table where it is massaged to remove extraneous data prior to it being compared with and appended to the live tables.

 

[milkman2500]

Why do you have to do this every month? Once the data is in Access, why is it being maintained in Excel?

My company maintains a proprietary database for the information. Employees use this database to make changes to their information.

The designers of the database created a standard excel report that can dump the data for manipulation. Unfortunately, I can't get them to create more customized reports. I need to use this standard, non-normalized excel spreadsheet, upload it into my access database and then create queries and reports from the data. My database is a personalized database used to create my own reports. No one else uses it. They only use the company's proprietary database.

 

[jdraw]

Are your reports important to the company?
Can you get your reports from the proprietary database that everyone seems to use, but you?

The approach you are wanting to take with

I'm attempting to build a database where I have all of the information in excel files, however I want to normalize the data in tables on a monthly basis.

seems to be a make work project without much design nor streamlining. Who do you think will maintain such a database?