I work for a financial institution and have been creating Excel Macros for the past few months to automate a few processes. I am now working on setting up an Access database to track financial transactions which is a daily process with weekly uploads of anywhere from 1,000 to 5,000 transaction records.
The data that is exported from a Web application in a fixed width or delimited excel file.
Each row contains a transaction record and there are multiple columns which store details for each transaction.
The problem I have is, the way the data is exported, there are multiple-rows for each record, each row storing additional information like other Names on the account, City State and Zip code. So each record is about 4 rows of data, instead of 1 row. My plan for now is to create a Macro to clean up the data in excel by making the 4 rows of data for each record, and making it 1 row for each record.
My main question is, would it be better to import and organize the data AS-IS in Access? Or to organize it in Excel before importing it?
The data that is exported from a Web application in a fixed width or delimited excel file.
Each row contains a transaction record and there are multiple columns which store details for each transaction.
The problem I have is, the way the data is exported, there are multiple-rows for each record, each row storing additional information like other Names on the account, City State and Zip code. So each record is about 4 rows of data, instead of 1 row. My plan for now is to create a Macro to clean up the data in excel by making the 4 rows of data for each record, and making it 1 row for each record.
My main question is, would it be better to import and organize the data AS-IS in Access? Or to organize it in Excel before importing it?
