If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Samora: Design of a table
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 |
#3
|
|||
|
|||
Samora: Design of a table
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] |
#4
|
|||
|
|||
Samora: Design of a table
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 |
#5
|
|||
|
|||
Samora: Design of a table
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 |
#6
|
|||
|
|||
Samora: Design of a table
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 |
Thread Tools | |
Display Modes | |
|
|