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


JulieD wrote:
> 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.t.w@juno.com> wrote in message
> news:1106816875.930344.316220@c13g2000cwb.googlegroups.com...
> > 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
> >