Samora: Design of a table

[Guest]

Hell

O.S. Windows XP Professiona
ACCESS ACCESS 200
EXCEL EXCEL 200

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 value
M1() , M2() , M3() , M4(

Something like that

0 76 77 78 .............. 174 25

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

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 CALC

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

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

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 al

Samora

 

[Brian Kastel]

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.

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).

 

[Brian Kastel]

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

 

[Guest]

Hi Bria

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 regard
Samora

 

[Brian Kastel]

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
(e-mail address removed)

--Original 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

 

[Guest]

Thanks a lot

i will send it them to yo

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

Any doubt, my e-mail is (e-mail address removed)

Once again, thanks alot for your hel

Best regard
José Santos