400+metrics: Span multiple wide tables or use one narrow table?

[Michael Deathya]

Hi,

I am pulling over 400 different metrics from an Excel spreadsheet into
Access (97). Conceptually, each row represents a single set of these
400 metrics. However, because of the 255 column limit in Access I am
faced with a dilemma:

Do I create two tables (1 with 255 columns, the other with 145) and a
lookup table that stores which metric is found in which table?
Obviously this method would require VBA logic to coordinate any time I
need to get at the data. Speed is not a huge issue so this could be
done.

Or do I create a table like so:
MetricSetID (number)
MetricName (text)
MetricValue (would have to be memo to accomodate numbers and text)

The advantage would be it can scale to an unlimited number of metrics
and I wouldn't need VBA to mess with the horrible two table system.
However, some metrics are memo-sized text chunks and some are floating
point numbers so I think I would have to make MetricValue a memo field
to accomodate both. This is obviously not ideal.

Any recommendations ?

Thank you,

Michael Deathya

[Mike MacSween]

"Michael Deathya" <de******@yahoo .com> wrote in message
news:5d******** *************** ***@posting.goo gle.com...

Hi,

I am pulling over 400 different metrics from an Excel spreadsheet into
Access (97). Conceptually, each row represents a single set of these
400 metrics. However, because of the 255 column limit in Access I am
faced with a dilemma: Do I create two tables (1 with 255 columns, the other with 145) and a
lookup table that stores which metric is found in which table?
No.
Or do I create a table like so:
MetricSetID (number)
MetricName (text)
MetricValue (would have to be memo to accomodate numbers and text)
That looks better.
However, some metrics are memo-sized text chunks and some are floating
point numbers so I think I would have to make MetricValue a memo field
to accomodate both. This is obviously not ideal.

Mmmm. What is it you're measuring? How are you getting a metric which is a
memo sized 'chunk' of text?

Tell us what the subject matter of this is. Good database design can only be
done with an understanding of the meaning and purpose of the information
that is being modelled. It's not a mechanical process.

Mike

[Tom]

Michael:

I don't have an answer for you, but I suggest you broaden your
consideration to the implications for Form and Report Design. I've
dealt with a similar situation for survey design with about 200
question and found that the form design for user input of data, report
design and general maintainability were as much of an issue as the
table design, and were driven by the underlying table design. Both
approaches had their difficulties, especially when dealing with
multiple data types.

FYI - I eventually went to the second method you mentioned because the
list of questions always seemed to be in flux.

Good luck

Tom

Michael Deathya wrote:

Hi,

I am pulling over 400 different metrics from an Excel spreadsheet into Access (97). Conceptually, each row represents a single set of these
400 metrics. However, because of the 255 column limit in Access I am
faced with a dilemma:

Do I create two tables (1 with 255 columns, the other with 145) and a
lookup table that stores which metric is found in which table?
Obviously this method would require VBA logic to coordinate any time I need to get at the data. Speed is not a huge issue so this could be
done.

Or do I create a table like so:
MetricSetID (number)
MetricName (text)
MetricValue (would have to be memo to accomodate numbers and text)

The advantage would be it can scale to an unlimited number of metrics
and I wouldn't need VBA to mess with the horrible two table system.
However, some metrics are memo-sized text chunks and some are floating point numbers so I think I would have to make MetricValue a memo field to accomodate both. This is obviously not ideal.

Any recommendations ?

Thank you,

Michael Deathya

[Rich P]

I would use 3 tables for your situation. The first 2 tables would each
contain 201 fields. The first field would be an ID field so you can
relate the 2 tables and then 200 fields in one table and the other 200
fields in Table2. The 3rd table would just contain a list of all the
fields, so you would have one text field in Table3 which would contain
400 rows.

When you start getting involved with big data like this you have to use
VBA code. The idea is that the machine will always do the work faster
than a human. If you have enough data, it could be impossible to
manipulate it by hand. Thus, the computer comes to the rescue. Sure,
for small data, doing things manually is easier than writing a bunch of
code. But for big data, you write the big code once and let the
computer do the work from then on.

Not to sound like I'm tooting my horn here, but to illustrate the point
of letting the computer do the work, I have automated several reports at
my company (big company) where it used to take weeks to develop just one
report by hand and to QA (ie, almost impossible to develop some of these
reports by hand). It took me a while to automate these reports (real
big data with lots of fields - not quite 400, more like 200, but over
2,000,000 records to go through), but now it takes a matter of minutes
to run each report (note: I am still not appreciated - at least, not
very much :).

As for data types for the table fields, you want to steer away from
using memo fields in tables with multiple data fields. It would be rare
to have several fields in Excel where people are entering more than 255
characters. Usually, you will have numeric, date, and small text fields
in Excel. So create your tables accordingly.

Another option would be to highlight and copy one half of the Excel
workbook and paste that into Access, then copy the other half of the
workbook and paste that into Access. Now you have your 2 data Tables.
Then just add the ID field to each table, either at the beginning of the
table or at the end.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[deathyam]

Mike,

Thank you for your response

I just replied to your post but then Google made me sign up for this
Beta version and my reply got lost. Brutal!
Here I go again:

My company has an Excel template that the owner of a product line uses
to plan for Marketing/Finance/Supply Chain. The finance metrics are
usually numbers and the marketing ones are often qualitative analysis
up to 1000 characters or so. There are about 50 product line owners and
each fills out a template. The idea is for them to click a save button
on the sheet and all the values are sucked up into Access where they
can be reported on/rolled up with others where applicable. Here's how I
planned to do it before I ran into the 255 column wall.

The template is organized visually, not at all conducive to pulling
data from it programmaticall y so here's how I did it:
Each cell that holds a value has an Excel Defined Name. I made the name
the same as a corresponding column in my Access table. Thus I iterate
through the Names collection in the workbook, grabbing the database
column name and the value to insert like so (all data stored in a VB
Collection):
Public Function LoadMetricsFrom Excel(wb As Workbook)
Dim nameMetric As Excel.Name

For Each nameMetric In wb.Names
Me.Add nameMetric.Name , nameMetric.Refe rsToRange.Value
Next
End Function

and then when I want to write them to Access, I create a giant INSERT
SQL statement.

I know it's bad style but I was stymied by the different datatypes when
I considered option 2.

Thanks,
Michael

[Mike MacSween]

Is every field filled in on for every product line?

<de******@yahoo .com> wrote in message
news:11******** *************@z 14g2000cwz.goog legroups.com...

Mike,

Thank you for your response

I just replied to your post but then Google made me sign up for this
Beta version and my reply got lost. Brutal!
Here I go again:

My company has an Excel template that the owner of a product line uses
to plan for Marketing/Finance/Supply Chain. The finance metrics are
usually numbers and the marketing ones are often qualitative analysis
up to 1000 characters or so. There are about 50 product line owners and
each fills out a template. The idea is for them to click a save button
on the sheet and all the values are sucked up into Access where they
can be reported on/rolled up with others where applicable. Here's how I
planned to do it before I ran into the 255 column wall.

The template is organized visually, not at all conducive to pulling
data from it programmaticall y so here's how I did it:
Each cell that holds a value has an Excel Defined Name. I made the name
the same as a corresponding column in my Access table. Thus I iterate
through the Names collection in the workbook, grabbing the database
column name and the value to insert like so (all data stored in a VB
Collection):
Public Function LoadMetricsFrom Excel(wb As Workbook)
Dim nameMetric As Excel.Name

For Each nameMetric In wb.Names
Me.Add nameMetric.Name , nameMetric.Refe rsToRange.Value
Next
End Function

and then when I want to write them to Access, I create a giant INSERT
SQL statement.

I know it's bad style but I was stymied by the different datatypes when
I considered option 2.

Thanks,
Michael

[Bob Quintal]

de******@yahoo. com wrote in
news:11******** *************@z 14g2000cwz.goog legroups.com:

Mike,

Thank you for your response

My company has an Excel template that the owner of a product
line uses to plan for Marketing/Finance/Supply Chain. The
finance metrics are usually numbers and the marketing ones are
often qualitative analysis up to 1000 characters or so. TThus I iterate through the Names collection in the workbook, grabbing the database column name and the value to
insert like so (all data stored in a VB Collection):
Public Function LoadMetricsFrom Excel(wb As Workbook)
Dim nameMetric As Excel.Name

For Each nameMetric In wb.Names
Me.Add nameMetric.Name , nameMetric.Refe rsToRange.Value
Next
End Function

and then when I want to write them to Access, I create a giant
INSERT SQL statement.

I know it's bad style but I was stymied by the different
datatypes when I considered option 2.

Thanks,
Michael

I'd create a metric table with five columns, based on your three
field concept.

The structure would be
MetricSetID (number)
MetricName (text)
MetricNumber
MetricText
MetricType (number or text)

and import data to the correct field, and set the MetricType to
show which field is used for that metric.

You could also move the MetricType field to a MetricNmae
definition table, and place the MetricName's key into this table
instead of the name itself.
--
Bob Quintal

PA is y I've altered my email address.

[deathyam]

Thanks Bob,

This seems like a good solution.

Cheers.
Michael