Excel > Access

[Tony9703]

My company currently has a large amount of data in Excel. We think we would
be better suited to have it in Access. However, we need to be able to import
new data on a monthly basis, while simultaneously maintaining the old data in
a monthly and YTD format. From time to time, new types and new categories of
data present themselves, which is something that we need to be able to
handle, as well. Lastly, this process should be as automated as possible.
We should not have to manually import vast quantities of data, nor should we
have to copy/paste the same data into different sections.

 

[Klatuu]

Everything you are wanting to do can be easily done and automatted. The
TransferSpreadsheet method or action is used for importing and exporting
Excel data.

The first issue you should address is the format of the data. Spreadsheets
and relational databases are very different. Importing spreadsheet data "as
is", usually becomes problematic. By that I mean working with data in
spreadsheet format is clumsy in Access. Start by defining the tables and
fields you will need with a relational model in mind. If you are not
familiar with relational database design, you would be well served to hire a
consultant to assist you with the design phase.

For example, in many spreadsheets you will have columns by month that has
transactional data. That is not how you would do it in a relational model.
You would have a transaction table and one of the fields would be the month
or date.

Once you have defined your table structure, you will need to create queries
that transpose the excel data into the relational structure.

Best of Luck.