Doing the math

  • Thread starter Thread starter UT_Lab_Sup
  • Start date Start date
U

UT_Lab_Sup

I would like to be able to set a Column in one of my queries that will give
me the abs(max() of a series of columns within the query .. I am having
trouble.. I can seem to get the query to do anything except either add or sub
the columns with in the query....
 
I'm having a bit of trouble picturing your need. Could you post a row or two of
data and your expected results

You may need to use a custom function to return the results you want since there
is no built in SQL function to get the maximum of fields (columns) within a row.
Is the number of columns fixed or variable?

It would also help if you could post the SQL of a query that gives you
everything except the value you are having trouble obtaining.
 
What am looking for is .. Something like this

I need one column in a query to be something like this abs(MAX([Field 1],
[Field2],[Field3],etc)

Here an SQL view of something I tried but didnt seem to work

INSERT INTO [Hamburg Db] ( Test, [Project Number], [Pass Number], RTD1, RTD10,
RTD11, RTD5, RTD4, RTD3, RTD2, RTD6, RTD7, RTD8, RTD9 )
SELECT Abs(Max(([Excel_Data!RTD1:RTD11]))) AS Expr1, Excel_Data.PROJECT_NAME,
Excel_Data.PASS_NUMBER, Excel_Data.RTD1, Excel_Data.RTD10, Excel_Data.RTD11,
Excel_Data.RTD5, Excel_Data.RTD4, Excel_Data.RTD3, Excel_Data.RTD2,
Excel_Data.RTD6, Excel_Data.RTD7, Excel_Data.RTD8, Excel_Data.RTD9
FROM Excel_Data
GROUP BY Excel_Data.PROJECT_NAME, Excel_Data.PASS_NUMBER, Excel_Data.RTD1,
Excel_Data.RTD10, Excel_Data.RTD11, Excel_Data.RTD5, Excel_Data.RTD4,
Excel_Data.RTD3, Excel_Data.RTD2, Excel_Data.RTD6, Excel_Data.RTD7,
Excel_Data.RTD8, Excel_Data.RTD9;

Where you can see after the SELECT I have Abs(Max(([Excel_Data!RTD1:RTD11])))
,
 
One question, do you want the absolute value of the largest value (max) or
do you want the largest value of the absolute values.
Given -23, 12, 3
Do you want 23 or 12?

Given -23, -12, -2
Do you want 23 or 2? The largest number is -2, the largest absolute value
is 23

Here is a little function to get the maximum value. So you could use it if
you wanted the largest number. If you wanted the largest absolute you could
call it as is or modify it.

Abs(fGetMaxNumber( Excel_Data.RTD1, Excel_Data.RTD2, Excel_Data.RTD3,...
,Excel_Data.RTD11) ) This gets the largest value and then returns the
absolute of the value

fGetMaxNumber( Abs(Excel_Data.RTD1), Abs(Excel_Data.RTD2),
Abs(Excel_Data.RTD3),..., Abs(Excel_Data.RTD11) ) This gets the absolute
value of each column and then returns the largest absoluter value

'------------- Code Starts --------------
Public Function fGetMaxNumber(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the Maximum Number of a group of values passed to it.
'Sample call: myMax = GetMaxNumber("-21","TEST","2", "3",4,5,6,"7",0)
returns 7
'Ignores values that cannot be treated as numbers.

Dim i As Integer, vMax As Variant, tfFound As Boolean, dblCompare As Double

vMax = -1E+308 'very large negative number
For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then
dblCompare = CDbl(Values(i))
If dblCompare > vMax Then
vMax = dblCompare
tfFound = True
End If
End If
Next

If tfFound Then
fGetMaxNumber = vMax
Else
fGetMaxNumber = Null
End If

End Function

UT_Lab_Sup via AccessMonster.com said:
What am looking for is .. Something like this

I need one column in a query to be something like this abs(MAX([Field 1],
[Field2],[Field3],etc)

Here an SQL view of something I tried but didnt seem to work

INSERT INTO [Hamburg Db] ( Test, [Project Number], [Pass Number], RTD1,
RTD10,
RTD11, RTD5, RTD4, RTD3, RTD2, RTD6, RTD7, RTD8, RTD9 )
SELECT Abs(Max(([Excel_Data!RTD1:RTD11]))) AS Expr1,
Excel_Data.PROJECT_NAME,
Excel_Data.PASS_NUMBER, Excel_Data.RTD1, Excel_Data.RTD10,
Excel_Data.RTD11,
Excel_Data.RTD5, Excel_Data.RTD4, Excel_Data.RTD3, Excel_Data.RTD2,
Excel_Data.RTD6, Excel_Data.RTD7, Excel_Data.RTD8, Excel_Data.RTD9
FROM Excel_Data
Click to expand...
Where you can see after the SELECT I have
Abs(Max(([Excel_Data!RTD1:RTD11])))
,

John said:
I'm having a bit of trouble picturing your need. Could you post a row or
two of
data and your expected results

You may need to use a custom function to return the results you want since
there
is no built in SQL function to get the maximum of fields (columns) within
a row.
Is the number of columns fixed or variable?

It would also help if you could post the SQL of a query that gives you
everything except the value you are having trouble obtaining.
Click to expand...
Click to expand...
 
I am looking for the Maximum absolote value .. So if I have -23,-3,-2 I am
looking 23

UT_Lab_Sup said:
What am looking for is .. Something like this

I need one column in a query to be something like this abs(MAX([Field 1],
[Field2],[Field3],etc)

Here an SQL view of something I tried but didnt seem to work

INSERT INTO [Hamburg Db] ( Test, [Project Number], [Pass Number], RTD1, RTD10,
RTD11, RTD5, RTD4, RTD3, RTD2, RTD6, RTD7, RTD8, RTD9 )
SELECT Abs(Max(([Excel_Data!RTD1:RTD11]))) AS Expr1, Excel_Data.PROJECT_NAME,
Excel_Data.PASS_NUMBER, Excel_Data.RTD1, Excel_Data.RTD10, Excel_Data.RTD11,
Excel_Data.RTD5, Excel_Data.RTD4, Excel_Data.RTD3, Excel_Data.RTD2,
Excel_Data.RTD6, Excel_Data.RTD7, Excel_Data.RTD8, Excel_Data.RTD9
FROM Excel_Data
GROUP BY Excel_Data.PROJECT_NAME, Excel_Data.PASS_NUMBER, Excel_Data.RTD1,
Excel_Data.RTD10, Excel_Data.RTD11, Excel_Data.RTD5, Excel_Data.RTD4,
Excel_Data.RTD3, Excel_Data.RTD2, Excel_Data.RTD6, Excel_Data.RTD7,
Excel_Data.RTD8, Excel_Data.RTD9;

Where you can see after the SELECT I have Abs(Max(([Excel_Data!RTD1:RTD11])))
,
I'm having a bit of trouble picturing your need. Could you post a row or two of
data and your expected results
Click to expand...
[quoted text clipped - 10 lines]
Click to expand...
 
I get a undefine function in expression error message ...

UT_Lab_Sup said:
What am looking for is .. Something like this

I need one column in a query to be something like this abs(MAX([Field 1],
[Field2],[Field3],etc)

Here an SQL view of something I tried but didnt seem to work

INSERT INTO [Hamburg Db] ( Test, [Project Number], [Pass Number], RTD1, RTD10,
RTD11, RTD5, RTD4, RTD3, RTD2, RTD6, RTD7, RTD8, RTD9 )
SELECT Abs(Max(([Excel_Data!RTD1:RTD11]))) AS Expr1, Excel_Data.PROJECT_NAME,
Excel_Data.PASS_NUMBER, Excel_Data.RTD1, Excel_Data.RTD10, Excel_Data.RTD11,
Excel_Data.RTD5, Excel_Data.RTD4, Excel_Data.RTD3, Excel_Data.RTD2,
Excel_Data.RTD6, Excel_Data.RTD7, Excel_Data.RTD8, Excel_Data.RTD9
FROM Excel_Data
GROUP BY Excel_Data.PROJECT_NAME, Excel_Data.PASS_NUMBER, Excel_Data.RTD1,
Excel_Data.RTD10, Excel_Data.RTD11, Excel_Data.RTD5, Excel_Data.RTD4,
Excel_Data.RTD3, Excel_Data.RTD2, Excel_Data.RTD6, Excel_Data.RTD7,
Excel_Data.RTD8, Excel_Data.RTD9;

Where you can see after the SELECT I have Abs(Max(([Excel_Data!RTD1:RTD11])))
,
I'm having a bit of trouble picturing your need. Could you post a row or two of
data and your expected results
Click to expand...
[quoted text clipped - 10 lines]
Click to expand...
 
Did you copy and paste the function into a vba module? Not in a form or
report class module.
Did you save the module with a name other than fGetMaxNumber?

INSERT INTO [Hamburg Db] ( Test, [Project Number], [Pass Number], RTD1,
RTD10, RTD11, RTD5, RTD4, RTD3, RTD2, RTD6, RTD7, RTD8, RTD9 )
SELECT fGetMaxNumber(Abs(Rtd1), Abs(Rtd2), Abs(rtd3),...,Abs(RTD11)),
Project_Name, Pass_Number, ...
FROM Excel_Data


Another option is to add a column in the excel table to get the max using
the Excel function. And then import or link that excel sheet.


UT_Lab_Sup via AccessMonster.com said:
I get a undefine function in expression error message ...

UT_Lab_Sup said:
What am looking for is .. Something like this

I need one column in a query to be something like this abs(MAX([Field 1],
[Field2],[Field3],etc)

Here an SQL view of something I tried but didnt seem to work

INSERT INTO [Hamburg Db] ( Test, [Project Number], [Pass Number], RTD1,
RTD10,
RTD11, RTD5, RTD4, RTD3, RTD2, RTD6, RTD7, RTD8, RTD9 )
SELECT Abs(Max(([Excel_Data!RTD1:RTD11]))) AS Expr1,
Excel_Data.PROJECT_NAME,
Excel_Data.PASS_NUMBER, Excel_Data.RTD1, Excel_Data.RTD10,
Excel_Data.RTD11,
Excel_Data.RTD5, Excel_Data.RTD4, Excel_Data.RTD3, Excel_Data.RTD2,
Excel_Data.RTD6, Excel_Data.RTD7, Excel_Data.RTD8, Excel_Data.RTD9
FROM Excel_Data
GROUP BY Excel_Data.PROJECT_NAME, Excel_Data.PASS_NUMBER, Excel_Data.RTD1,
Excel_Data.RTD10, Excel_Data.RTD11, Excel_Data.RTD5, Excel_Data.RTD4,
Excel_Data.RTD3, Excel_Data.RTD2, Excel_Data.RTD6, Excel_Data.RTD7,
Excel_Data.RTD8, Excel_Data.RTD9;

Where you can see after the SELECT I have
Abs(Max(([Excel_Data!RTD1:RTD11])))
,
I'm having a bit of trouble picturing your need. Could you post a row or
two of
data and your expected results
Click to expand...
[quoted text clipped - 10 lines]
trouble.. I can seem to get the query to do anything except either add
or sub
the columns with in the query....
Click to expand...
Click to expand...
Click to expand...
 
I am beginning to think that formatting the data the way I want it would be
easier in Excel and then just importing it into Access.. But I am trying to
make this as user hands off as possible .. and I think that using 2
applications is going to complicate things...

John said:
Did you copy and paste the function into a vba module? Not in a form or
report class module.
Did you save the module with a name other than fGetMaxNumber?

INSERT INTO [Hamburg Db] ( Test, [Project Number], [Pass Number], RTD1,
RTD10, RTD11, RTD5, RTD4, RTD3, RTD2, RTD6, RTD7, RTD8, RTD9 )
SELECT fGetMaxNumber(Abs(Rtd1), Abs(Rtd2), Abs(rtd3),...,Abs(RTD11)),
Project_Name, Pass_Number, ...
FROM Excel_Data

Another option is to add a column in the excel table to get the max using
the Excel function. And then import or link that excel sheet.
I get a undefine function in expression error message ...
Click to expand...
[quoted text clipped - 31 lines]
Click to expand...
 
Back
Top