Samora: Design of a table

Hello

O.S. Windows XP Professional
ACCESS ACCESS 2003
EXCEL EXCEL 2003

i have an Excel that supports 4 great calculations (mentioned as CALC1 , CALC2 , CALC3, CALC4)

CALC1 has a range of values 0 ; 76-174 ; 250 and then by formulas i can calculate, for each of those values
M1() , M2() , M3() , M4()

Something like that:

0 76 77 78 .............. 174 250

M1 0 1,30 1,31 1,32 1,01 1,00
M2 0 2,00 2,01 2,05 2,06 2,04
M3 0 3,00 3,01 3,02 3,04 3,03
M4 0 4,00 4,03 4,10 4,09 4,10

CALC2 is another sheet i have but it has only the first field (ID) and the rest are the results i obtain on the same range of data i have in CALC1. Alias, some of the formulas have values from the data i obtain in CALC1

CALC3 and CALC4 are equal to CALC2 , only the formulas change.

CALC2, CALC3 and CALC4 has 324 lines x 103 columns each one

The result of each table is to calculate the MAX and MIN for each table (CALC2 , CALC3 , CALC4) to be used in a form i have allready working to perform some calculations.

The first thing that occured to me is to make a table as they are, importing them to ACCESS and then in My form put the fields i need and make the calculation, but i want to gain some performance while doing those calculations and as u can see is a large ammount of data (Even in Access takes a little while to obtain the results).

Anyone have any sugestion to make those 4 tables in ACCESS ?

Best Regards to all

Samora

The development of a database for this requires a fundamentally different
approach than would be used in a spreadsheet. Nevertheless, here is my
suggestion:

Coincidentally, this design uses four tables, but only because your dataset
is four-dimensional. It would still use four tables even if you had only 2
sheets or as many as 42 sheets.

Create the tables:

One for the groups of data (sheets)

Example:

tblCalcMethods
----------------
CalcID (Autonumber)
CalcDesc (Text)

Sample Data
-------------
CalcID CalcDesc
1 CALC1
2 CALC2
3 CALC3
4 CALC4

Another for the M() values (row headings on each sheet)

tblMIndexes
------------
MID (Autonumber)
MDesc (Text)

Sample Data
-------------
MID MDesc
1 M1()
2 M2()
3 M3()

A third for the range values (column headings on each sheet)

tblRanges
------------
RangeID (Autonumber)
RangeValue (Byte)

Sample Data
-------------
RangeID RangeValue
1 0
2 76
3 77
4 78

And a fourth containing the Min/Max values pertinent to the three conditions
above

tblMinMax
-----------
MinMaxID (Autonumber)
CalcID (Long Integer)
MID (Long Integer)
RangeID (Long Integer)
Min (Integer)
Max(Integer)

Sample Data
-------------
MinMaxID CalcID MID RangeID Min Max
1 1 1 1 0 0
1 1 1 2 1 30
1 1 1 3 1 31
1 1 1 4 1 32

tblCalcMethods, tblMIndexes, and tblRanges would each have a one-to-many
relationship to tblMinMax. Each cell of data in your spreadsheets would
then require a unique record in tblMinMax. From there, you could have a
single form with three listboxes and their RowSource properties set to
tblCalcMethods, tblMIndexes and tblRanges, respectively, two columns each,
with the column width properties set to 0" (this hides the key column). The
RecordSource property for the form would be set to tblMinMax, and the form
would also contain two textboxes with the ControlSource properties set to
Min and Max, respectively. By adding some code to the form, you can move to
the exact record to see the relevant Min/Max values just by clicking on the
list items.

I'm not sure if this helps or not.

It would be a lot easier if your Min/Max values were based on a mathematical
relationship to the row and column headings, but I suppose if that were the
case, then you wouldn't have the problem.


"Samora" wrote in message
...
Hello

O.S. Windows XP Professional
ACCESS ACCESS 2003
EXCEL EXCEL 2003

i have an Excel that supports 4 great calculations (mentioned as CALC1 ,
CALC2 , CALC3, CALC4)

CALC1 has a range of values 0 ; 76-174 ; 250 and then by formulas i can
calculate, for each of those values
M1() , M2() , M3() , M4()

Something like that:

0 76 77 78 .............. 174
250

M1 0 1,30 1,31 1,32 1,01 1,00
M2 0 2,00 2,01 2,05 2,06 2,04
M3 0 3,00 3,01 3,02 3,04 3,03
M4 0 4,00 4,03 4,10 4,09 4,10

CALC2 is another sheet i have but it has only the first field (ID) and the
rest are the results i obtain on the same range of data i have in CALC1.
Alias, some of the formulas have values from the data i obtain in CALC1

CALC3 and CALC4 are equal to CALC2 , only the formulas change.

CALC2, CALC3 and CALC4 has 324 lines x 103 columns each one

The result of each table is to calculate the MAX and MIN for each table
(CALC2 , CALC3 , CALC4) to be used in a form i have allready working to
perform some calculations.

The first thing that occured to me is to make a table as they are,
importing them to ACCESS and then in My form put the fields i need and make
the calculation, but i want to gain some performance while doing those
calculations and as u can see is a large ammount of data (Even in Access
takes a little while to obtain the results).

Anyone have any sugestion to make those 4 tables in ACCESS ?

Best Regards to all

Samora

Tiny correction:

My sample data for tblMinMax should have incremented values for the MinMaxID
field, but those are autonumber values so in practice they would be
generated as such.

e.g.:

Sample Data
-------------
MinMaxID CalcID MID RangeID Min Max
1 1 1 1 0 0
2 1 1 2 1 30
3 1 1 3 1 31
4 1 1 4 1 32


"Brian Kastel"
wrote in message ...

[snip]

tblMinMax
-----------
MinMaxID (Autonumber)
CalcID (Long Integer)
MID (Long Integer)
RangeID (Long Integer)
Min (Integer)
Max(Integer)

Sample Data
-------------
MinMaxID CalcID MID RangeID Min Max
1 1 1 1 0 0
1 1 1 2 1 30
1 1 1 3 1 31
1 1 1 4 1 32

[snip]

Hi Brian

I am trying to understand your reply to ensure myself of the solution.

nevertheless , thanks a lot for your help.

i will keep in touch.

best regards
Samora

If you send your spreadsheet to me, I will convert it to the structure I
suggested for you.

My email address is spelled out below.

--

Brian Kastel


--Original Message----------------

"Samora" wrote in message
...
Hi Brian

I am trying to understand your reply to ensure myself of the solution.

nevertheless , thanks a lot for your help.

i will keep in touch.

best regards
Samora

Thanks a lot

i will send it them to you

for the calculations we are talking about u must see the sheet named Placfort F

Any doubt, my e-mail is

Once again, thanks alot for your help

Best regards
José Santos