How do I get min max from 100 cuts in one record?

[Robert Waggoner]

We have a precision cut database that measures the cut width. We set the
database up with 100 fields (one for each possible cut). Some of the fields
are going to be Null. We need to analyze the record to find the min and max,
average, and statistical deviation. Is there a way to do this in Access 97?
I see all kinds of examples for record by record but nothing within a single
record.

My fields start with
Cut1
Cut2, etc.

It wasn't the best way to set the database up but since it was inherited I
wonder if anyone can help me salvage this.

[pietlinden]

Robert Waggoner wrote:

We have a precision cut database that measures the cut width. We set the
database up with 100 fields (one for each possible cut). Some of the fields
are going to be Null. We need to analyze the record to find the min and max,
average, and statistical deviation. Is there a way to do this in Access 97?
I see all kinds of examples for record by record but nothing within a single
record.

My fields start with
Cut1
Cut2, etc.

It wasn't the best way to set the database up but since it was inherited I
wonder if anyone can help me salvage this.

I'd probably normalize to a new table and then summarize that.
Otherwise, you're going to have a horrendous time trying to get the
data together. You can use DAO to get data nd fieldnames and then use
some basic logic to flatten to something like (MeasurementType,
Measurement). then you can do stats on that.

[Robert Waggoner]

Thanks! But I'm not sure how to write the code to create the new table. Any
help getting started would be appreciated.
<pi********@hotmail.comwrote in message
news:11**********************@s13g2000cwa.googlegr oups.com...

>
Robert Waggoner wrote:

>We have a precision cut database that measures the cut width. We set the
database up with 100 fields (one for each possible cut). Some of the
fields
are going to be Null. We need to analyze the record to find the min and
max,
average, and statistical deviation. Is there a way to do this in Access
97?
I see all kinds of examples for record by record but nothing within a
single
record.

My fields start with
Cut1
Cut2, etc.

It wasn't the best way to set the database up but since it was inherited
I
wonder if anyone can help me salvage this.

I'd probably normalize to a new table and then summarize that.
Otherwise, you're going to have a horrendous time trying to get the
data together. You can use DAO to get data nd fieldnames and then use
some basic logic to flatten to something like (MeasurementType,
Measurement). then you can do stats on that.

[pietlinden]

Robert Waggoner wrote:

Thanks! But I'm not sure how to write the code to create the new table. Any
help getting started would be appreciated.

Creating the table isn't the problem.

Something like
CutID
CutType
CutValue

"CutType" would correspond to your column name in your current table.
"CutValue" would correspond to the number/value in the non-null field.

The way I would go about solving it is creating and executing a series
of SQL statements in code that select the non-null value "pairs" and
then inserting them into a normalized table using CurrentDB.Execute
<SQL statement>. Then the code would switch the fields and you'd be
done in no time.

This is what I've come up with so far...

<Note to serious developers... put the coffee down, finish
swallowing... I don't want to be responsible for hot-beverage-related
mishaps.

'----CODE START------------------
Option Compare Database

Public Sub NormalizeData()

Dim tdf As DAO.TableDef
Dim strFieldName As String
Dim intCounter As Integer

Const cSQL As String = "INSERT INTO tblCutsFinal (ID, MyValue,
MyFieldName) SELECT tblNonNormalCuts.MyID,"
Dim strSQL As String

Set tdf = DBEngine(0)(0).TableDefs("tblNonNormalCuts")

'---Loop through the field pairs fields(0) and fields(intCounter),
'--- and append them to the final table

For intCounter = 1 To tdf.Fields.Count - 1

'-----Build Insert statement

strSQL = cSQL & "[" & tdf.Name & "].[" &
tdf.Fields(intCounter).Name & "],'" & tdf.Fields(intCounter).Name & "'
FROM [" & tdf.Name & "] "
strSQL = strSQL & "WHERE ((([" & tdf.Name & "].[" &
tdf.Fields(intCounter).Name & "] IS NOT NULL)));"
'----Execute it
DBEngine(0)(0).Execute strSQL, dbFailOnError
Next intCounter

Set tdf = Nothing

End Sub

'----CODE END...

[Rich P]

Hi Robert,

With all due respect, your data is classic of a non normalized table.
The issue here is that it becomes way more difficult to perform sql
operations on non-normalized tables. A non-normalized table is a table
that contains redundant columns. Based on your description, your table
should only have 2 columns (3 columns if you want to include an autonum
column)

cutType CutWidth
cut1 .5
cut2 .7
cut3 3
cut4 .6
cut5 16
...
cut100 4.6

Now you can use the sql functions from an Access query very easily to
perform calculations on the widths.

Access can manage 100,000 rows way easier than 100 columns. The reason
you have not found any examples on how to use sql agains columns is
because sql is designed to be used against rows.

If you plan on using your DB in production, you (or someone) need to
normalize it first. Even if that is a big hassle, as you can see,
dealing with an non-normalized DB is a bigger hassel.

HTh

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Robert Waggoner]

Thank you! I'll begin testing this today.
<pi********@hotmail.comwrote in message
news:11**********************@75g2000cwc.googlegro ups.com...

>
Robert Waggoner wrote:

>Thanks! But I'm not sure how to write the code to create the new table.
Any
help getting started would be appreciated.

Creating the table isn't the problem.

Something like
CutID
CutType
CutValue

"CutType" would correspond to your column name in your current table.
"CutValue" would correspond to the number/value in the non-null field.

The way I would go about solving it is creating and executing a series
of SQL statements in code that select the non-null value "pairs" and
then inserting them into a normalized table using CurrentDB.Execute
<SQL statement>. Then the code would switch the fields and you'd be
done in no time.

This is what I've come up with so far...

<Note to serious developers... put the coffee down, finish
swallowing... I don't want to be responsible for hot-beverage-related
mishaps.

'----CODE START------------------
Option Compare Database

Public Sub NormalizeData()

Dim tdf As DAO.TableDef
Dim strFieldName As String
Dim intCounter As Integer

Const cSQL As String = "INSERT INTO tblCutsFinal (ID, MyValue,
MyFieldName) SELECT tblNonNormalCuts.MyID,"
Dim strSQL As String

Set tdf = DBEngine(0)(0).TableDefs("tblNonNormalCuts")

'---Loop through the field pairs fields(0) and fields(intCounter),
'--- and append them to the final table

For intCounter = 1 To tdf.Fields.Count - 1

'-----Build Insert statement

strSQL = cSQL & "[" & tdf.Name & "].[" &
tdf.Fields(intCounter).Name & "],'" & tdf.Fields(intCounter).Name & "'
FROM [" & tdf.Name & "] "
strSQL = strSQL & "WHERE ((([" & tdf.Name & "].[" &
tdf.Fields(intCounter).Name & "] IS NOT NULL)));"
'----Execute it
DBEngine(0)(0).Execute strSQL, dbFailOnError
Next intCounter

Set tdf = Nothing

End Sub

'----CODE END...