Need Advice on importing a LARGE excel spreadsheet into Access 2000
Hi all,
I hope I can do an adequate job of explaining what I'm trying to do.
At work we have a rather large (and growing) Excel spreadsheet that
really should be in a database. This spreadsheet contains data that
should be in several different tables. I am quite proficient in Excel
so I can easily manipulate the spreadsheet and break the data into
separate worksheets and then import it into Excel. The problem is I
don't want to do this twice...
I think it will take me a few weeks to get the database built and
doing everything I want it to do. The Excel file in question is
changing everyday. I am working on this project at home and at school
so I don't see much of a way to link Access to an Excel file at work -
though that may be workable. Also, eventually I want to get rid of
the Excel spreadsheet altogether. If I link to it, will it be
possible to easily eliminate it?
Another issue I'm having is trying to figure out how to import and set
up one of the tables. Assume my column headings and data look like
this in Excel:
ID Jan-02 Feb-02 Mar-02
---- ------ ------ ------
123 1/3/02 2/5/02 2/25/02
124 1/5/02 2/2/02 3/1/02
Where the ID might be a tenant ID and under the month heading is the
date the rent payment was received. I *think* I want import the data
into a table with the ID being a common field (related to say a Tenant
table) and the month and date the payment was received as fields. So
for tenant 123 I would have three records. My table would look
something like this:
ID Month Rec'd
---- ------ ------
123 Jan-02 1/3/02
123 Feb-02 2/5/02
123 Mar-02 2/25/02
My project actually has nothing to do with tenants and rent, but I
think this is a good analogy (and much easier to explain). I'm sure
this has to be a fairly common problem and I'm probably overlooking
something simple, but I am just beginning to learn Access.
Any thoughts or advice will be greatly appreciated.
Thanks,
Jay