Access-friendly spreadsheet data

[Robert F]

I have an excel spreadsheet that I need to be able to translate into
something Access can use. The problem I have is that there are both row and
column headings (like a pivot table) and the spreadsheet field is a non
calculated answer. The data is static and used in other calculations. Please
tell me there is an answer besides if/then statements for every possible
outcome!

 

[Jeff Boyce]

Robert

I'm not sure from your description, but it sounds like your "spreadsheet
field" values can be categorized by two values, the row-title and the
column-title.

If so, one approach might be to import the data as is, for further
processing.

The further processing would be done using queries, and would append records
to a more permanent (and normalized) table.

One way to handle this parsing would be to select your second field (first
field is row-headers, second field is first column of actual data) of data,
append the "category" contained in the first column and the data in the
second column, and append, in the same query, the value contained in the
Column-header of that second field/column.

Then repeat, for the next column of data (appending the column-header of
THAT column).

"Rinse & repeat" until done.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Robert F]

I'n not quite understanding. If I did a series of appends like that, I would
have the row the data came off of, but how would I know the column? Or would
I need to create a new table for each append query?

 

[John W. Vinson]

I'n not quite understanding. If I did a series of appends like that, I would
have the row the data came off of, but how would I know the column? Or would
I need to create a new table for each append query?

Spreadsheet logic dictates "wide-flat" designs. Relational databases work
better with "tall-thin".

A normalized table for your data would have three fields - one for the row
identifier, one for the column head, and one for the value at their
intersection. Instead of 100 rows with 100 fields, you'ld have 10000 rows with
3 fields.

John W. Vinson [MVP]