Excel spreadsheet to to access

[anna_717717]

Hi,

I've got some data in excel spreadsheet which looks something like this:
a1 a2 a3
b1 X
b2 y z
b3 j k z

but with many more rows and columns!
I need to convert it into access to do other manipulation on the data.

I think that I would need to store the data something like:
b1 a1 x
b2 a1 y
b2 a2 z

etc. but i'm not sure of the best way of setting up the tables and queries.

I would also ideally still need to be able to edit the data in the same
format as it would look in a spreadsheet.

Any suggestions please?

 

[Douglas J. Steele]

Unfortunately, it's not really possible to store data like that properly
normalized (what you've suggested is correct, btw), and have an updatable
form that looks like a spreadsheet.

What I've done in the past is created a temporary table (in a temporary
database, to avoid database bloating), populated that temporary table with
the data from my "real" table just before the user went into the form, then
used VBA code to update the real table with any changes made. A bit of work,
but my users seemed to like it.

 

[anna_717717]

Hi, thanks for the quick response

The tempory table sounds like it could be what I need, have you got any
guidelines on how I could do this please.

 

[Douglas J. Steele]

I typically create a crosstab query (use the wizard to do this), then use
that as the basis for a make table query.

To put the temporary table in a temporary database, see what Tony Toews has
at www.granite.ab.ca/access/temptables.htm