Prepare Spreadsheet for Import into Access Table

J

j.t.w

Hello Everyone,

I'm trying to prepare some data that is currently in an Excel
spreadsheet to be imported into an Access table. The data (information)
looks fine and somewhat makes sense as you look at it as a spreadsheet
but, it won't import well into a table unless I move cells around
before I import. I would like to automate this process if at all
possible, as there are many spreadsheets in this format.

Here is what the spreadsheet looks like...

.. A B C D E F G H I J K L
1 101 John Doe 123 102 Jane Doe 459 103 Mary Smith 985
2 456 126 653
3 789 378 167
4 159 453 439

The numbers in column D, H, and L are dollar amounts. The amounts in
column D belongs to 101 John Doe. The amounts in column H belongs to
102 Jane Doe. The amounts in column L belongs to 103 Mary Smith.

Here is what I would like...

.. A B C D E F G
1 101 John Doe 123 456 789 159
2 102 Jane Doe 459 126 378 453
3 103 Mary Smith 985 653 167 439

There are usually more than 3 people(typically 12 to 20, or sometimes
even more), which is why I would like to automate this. Could someone
point me in the right direction or have any suggestions on how to
accomplish this? Should this be done on the Excel or Access side? Any
and all help would be appreciated.

Thank you.
j.t.w
 
J

JulieD

hi

admittedly without knowing your data (and not trying to be rude or
anything), i find it interesting that you want to export in the format that
you do, as i would think that this indicates a non-normalised database ... i
would have thought that in an access database the person information
(columns A, B & C) would go in one table with column A as the primary key
and the payments would go in another table (columns A & D) where each
payment was a separate record. If your database was set up like that it
would make importing the data quite easy. However, as i said i don't know
your data so i might be totally off-track here - but if you'ld like to
explore this concept further if you'ld like to outline the purpose of your
database i'll be happy to discuss it with you.

Alternatively, going with what you originally asked - does each person only
ever (and always) have four payments or are there more?

Cheers
JulieD
 
J

j.t.w

Hi JulieD,

Thank you for responding. Sorry I didn't get back with you earlier.

You are absolutely correct about importing the data where "the person
information (columns A, B & C) would go in one table with column A as
the primary key and the payments would go in another table (columns A &
D) where each payment was a separate record". I wasn't thinking when I
posted my question.

Could you help me with making this a reality? How would I go about
preparing the excel spreadsheet to import the data as you suggest?

As of right now (as far as I can tell), there are only four dollar
amounts per person, which hopefully makes this easier. Although,
sometimes there are blank dollar amounts in the 2nd, 3rd, or 4th rows,
which I would consider 0.

Again, thanks for your help.

j.t.w
 
Top