How to find the MAX. Value in 1 record, 12 fields

Gurbo

New member
Local time
Today, 07:30
Joined
Jan 13, 2003
Messages
8
Hi,

I've got a project dealing with various Decision Making Algorithms.

Wondering the VBA code of ;

Selecting the MAX. value from a table that has 1 record, which has 12 user-input (integer) attributes .

ex: Table tblWgtFactors >

Var1 Var2 Var3 Var4 Var5 Var6 Var7 Var8 Var9 Var10 Var11 Var12
------------------------------------------------------------------------------------
4 3 6 4 2 3 3 1 2 3 4 5

this VBA code should return value <6> , and put it in a new table tblMax_weight :confused:



PS : ( i've tried to solve it by Linking Excel <---> Access , but having Update problems ,, )



Thnx,,
 
You need to normalize your Weight table. Your table should have 2 fields (ID and WgtFactors) and 12 records, one for each Weight Factor not one record with 12 fields. Now you can easily find the number 6 using a query, DMax() function or other tool.

hth,
Jack
 
Public Sub MaxIt()
Dim dbs As Database
Dim rst As RecordSet
Dim i as Byte
Dim MaxVal As Double

Set dbs = CurrentDB
Set rst = dbs.OpenRecordSet("tblWgtFactors")
MaxVal = 0
For i = 0 to 11
If rst.Fields(i) > MaxVal Then MaxVal = rst.Fields(i)
Next i

rst.close
Set rst = dbs.OpenRecordSet("tblMax_weight")
rst.Edit
rst.Fields(0) = MaxVal
rst.Update
rst.close

End Sub
 
Voila, no record. All 0s

The code has an error for working with the recordsets i think , it clears the new data from the table (which contains the variable, that are needed for finding the Max value) ... it empties the table
 
Nope,
Created the tables, tblMax_weight & tblWgtFactors. Put 12 values in 12 distinct fields in tblWgtFactors. It does write over the current value in tblMax_weight, true, but only compares the values in tblWgtFactors.
 
The variables are entered on a form which update tblWgtFactors. A cmd button activates the code which then initializes the table
 
Gurbo,

The prior advice by Jack is good. Your table structure should
be as he says.

pdx-man's code is also good.

I think that the values don't exist in the table when you hit
your command button. They exist only on the form.

Bound? Unbound?

Why a table with 1 record/12 fields?

If the data is that transient, then maybe only a form is
needed.

I'm confused.
Wayne
 
I am also working with Gurbo on this project. Jack's solution is leading to change the structure of the project.

The first thing we have tried was to link with an Excel spreadsheet, where the max value (the maximum weight entered by the user) in a row could be found easier by using the max function. However, we could not succeed in updating the weights in the Excel spreadsheet just as the user enters the weights in the Access form.

1 record is used because all the 12 values just represent weights, named as var1, var2,...,var12 in our case.

Somehow it clears the wght_table that was full beforehand while taking the recordset.

Trying to find another way to do this...

Thanks for your consideration
 
boxster,

This must be a small database ...

ZIP it and post it. You'll get some response.

Wayne
 
I agree with Jack and Wayne. Take a look at your structure. But if you can't change it, take a look at your tables. Delete the Excel links, put some dummy data in the Excel Workbook, re-link. I have tested this code with Excel linked tables and still the code works fine for me ...
 
ok ,

Thnx all for your interest and patience ...


Problem is with Form > frmEnterLexio
That should not work becouse i've broke Excel<-->Access link.

Table > tblWgtFactors gets values from frmEnterLexio

Queries > Max_Wght & Select_MaxWght refers to Table> Sheet3 , but i've deleted it , as it was a link-table from Excel .xls ( u can check Sheet3 from .xls file in the ZIP)

What i need is to calculate MAX value of tblWgtFactors within Access , becouse Excel Automatic Update is not working properly , and this way is more proper 4 the project :p

Solution is : Somehow find the max , and create a TAble named Sheet3 with only MAX value.. I'll handle the rest

http://it.isikun.edu.tr/orkuni/images/dm.zip


Thnx.
 
Last edited:
Anyone here seen the movie Ice Age? This whole thing reminds me of the Dodo Birds scene running around trying to get the mellon. :D

If you can't restructure according to what Jack and Wayne have provided, then use my code. You have built objects, then deleted links and re-created other objects and tied to them and some stuff goes out to excel ... eeks! Start back at square one.

Create a new table, tblWgtFactors, with the 12 fields, all Numeric data types with Field Size Double.
Create another table, tblMax_weight, with one Numeric data type, field size Double.
Create your form based on tblWgtFactors
Have a button on there that will run the code after the record has been saved.

Get this working first, then add the Excel stuff, if you need to.

(I can't open your DB, as I am working in A97)
 
pdx-man -

I haven't seen the movie but I have a good imagination!

Jack
 
pdx_man ,

We do not remember that we have asked how to create tblWgtFactors & tblMax_Wgt blah bla ...

Our database is attached , and what we need is to calculate MAX value from tblWgtFactors within Access , and put the result into a new Table > named Sheet3.. Thats all !

If u don't have a solution, either cant open a DB in Access Forum , please do not make people to imagine Ice Age. It's not the place to make fun , and let people to imagine movies .. if u like to do so , i can supervise your imagination l8er , in another forum among my experiences ...

gurbo & boxster
 
Already gave you the solution ... Don't need rhetoric from those I try to help ... just trying to point out the flaws. I'm sorry, I thought you needed help.

Signing off ...
 
pdx_man's code is good. Since you do not use Access97, you have to make a reference to the Microsoft DAO 3.6 Object Library (when the code window is open, choose menu Tools, References...) and in the code specify the database and recordset as DAO's.


I have added:-
A table "tblMax_Weight"
A query "xx query_tblMax_Weight"
A button on form frmEnterLexio (which saves the record, runs pdx_man's code, and queries the max value placed in tblMax_Weight):-

------------------------------------------
Private Sub Command53_Click()
DoCmd.RunCommand acCmdSaveRecord

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim i As Byte
Dim MaxVal As Double

Set dbs = CurrentDb
Set rst = dbs.OpenRecordSet("tblWgtFactors")
MaxVal = 0
For i = 0 To 11
If rst.Fields(i) > MaxVal Then MaxVal = rst.Fields(i)
Next i

rst.Close
Set rst = dbs.OpenRecordSet("tblMax_Weight")
rst.Edit
rst.Fields(0) = MaxVal
rst.Update
rst.Close

DoCmd.OpenQuery "xx query_tblMax_Weight"

End Sub

----------------------------------------------

Just open frmEnterLexio (had to remove the image from the form to meet the size limit here), enter some values in the text boxes and click on the newly added button.
 

Attachments

Last edited:

Users who are viewing this thread

Back
Top Bottom