Text cells in Excel, crosstabs? drilling down? Conversion?

[Jim]

I have a workbook with 100 identical format worksheets.
Each worksheet is an educational course. There are 25 row
labels down the left representing 25 different
competencies we need to teach. There are about 12 column
labels to indicate if the course helps teach the
competency and how it is measured if it is taught (such as
written exam, oral exam, lab practical, specific
competency on patient care, etc.) The cells only have
text data (many cells have no data whatsoever). Is there
any way to take advantage of something like a crosstab for
numerical data using excel? If excel cannot do this, is
there a way to import a 100 worksheet file into Access to
do something similar? Thanks for any suggestions.

Jim

 

[John Nurick]

Hi Jim,

In Access a layout such as you describe would normally be the *result*
of a crosstab or some such, not the raw material for one. And you don't
mention any related data that could be drilled down into.

Storing the data you've described in a relational database system such
as Access would involve several related tables:

tblCourses - one record for each of the 100 courses

tblCompetencies - one record for each of the 25 competencies (are there
really only 25 competencies distributed across 100 courses?)

tblMeasurementMethods - one record each for written exam, oral exam,
etc.

tblCourseCompetenciesMeasurements - one record for each way each
competency is measured in each course, related to tblCourses,
tblCompetencies, and tblMeasurementMethods (each corresponding to a
column header in a worksheet).

tblCoursesCompetencies - one record for each competency delivered by
each course (each corresponding to a populated cell in one of your 100
worksheets)

To get to this from the 100 worksheets, I'd write VBA code to import all
the worksheets into a single temporary table, with an extra field for
"Course" which would in the first instance contain the worksheet name.
Then I'd use a series of append queries to normalise the data into the
structure of related tables.