Why does my sql not work with the Abs function when it works with the Max function?

I have a table [tblFull1 04WoodsideTransportV335] that all the data comes from. I want to get the [Data Location] and the [load_case] where the stress is either Max, Min or Abs. It works with Max and with Min. But for some reason it does not work with Abs. It is running in ms Access 2013. I generate the queries programmatically using vba.

Is there any difference between Abs and max, min operator that makes it not working?

When the Abs query run, for some reason it ads the "As Expr1", why would it do that?

I know the SQL looks bad, but it works. If you have any suggestions on how to improve it, please tell me.

Sorry for my bad English, I am Norwegian.

SQL with MAX function:

SELECT DISTINCT 
    t1.[load_case], t2.[Data Location] AS Nodenr, 
    t2.[MaxOfHoop Stress N/mm^2]
FROM 
    [tblFull1 04WoodsideTransportV335] AS t1,
    (SELECT 
         [tblFull1 04WoodsideTransportV335].[Data Location],
         MAX([tblFull1 04WoodsideTransportV335].[Hoop Stress N/mm^2]) AS [MaxOfHoop Stress N/mm^2]
     FROM 
         [tblFull1 04WoodsideTransportV335]
     GROUP BY 
         [tblFull1 04WoodsideTransportV335].[Data Location]
     HAVING ((([tblFull1 04WoodsideTransportV335].[Data Location])
        In (SELECT [Data Location]
            FROM [tblFull1 04WoodsideTransportV335]
            GROUP BY [Data Location]
            HAVING Count(*) > 1)) 
       AND ((MAX([tblFull1 04WoodsideTransportV335].[Hoop Stress N/mm^2])) Is Not Null))
     ORDER BY 
         [tblFull1 04WoodsideTransportV335].[Data Location]) AS t2
WHERE 
    ((t1.[Hoop Stress N/mm^2]) = [t2].[MaxOfHoop Stress N/mm^2])
ORDER BY 
    t1.[load_case] DESC;

SQL with Abs function:

SELECT DISTINCT 
    t1.load_case, t2.[Data Location] AS Nodenr, 
    t2.[AbsOfHoop Stress N/mm^2] AS Expr1
FROM 
    [tblFull1 04WoodsideTransportV335] AS t1,
    (SELECT 
         [tblFull1 04WoodsideTransportV335].[Data Location],
         ABS([tblFull1 04WoodsideTransportV335].[Hoop Stress N/mm^2]) AS[AbsOfHoop Stress N/mm^2]
     FROM [tblFull1 04WoodsideTransportV335]
     GROUP BY [tblFull1 04WoodsideTransportV335].[Data Location]
     HAVING ((([tblFull1 04WoodsideTransportV335].[Data Location])
                In (SELECT [Data Location]
                    FROM [tblFull1 04WoodsideTransportV335] 
                    GROUP BY [Data Location]
                    HAVING Count(*) > 1)) 
        AND ((ABS([tblFull1 04WoodsideTransportV335].[Hoop Stress N/mm^2])) Is Not Null))
     ORDER BY 
         [tblFull1 04WoodsideTransportV335].[Data Location]) AS t2
WHERE 
    (((t1.[Hoop Stress N/mm^2])=[t2].[AbsOfHoop Stress N/mm^2]))
ORDER BY 
    t1.load_case DESC;

Answers


In SQL, in "HAVING" statment, you can only use columns defined in "GROUP BY". For other columns, you must use then within Aggregate Functions, which are: - AVG() - Returns the average value - COUNT() - Returns the number of rows - FIRST() - Returns the first value - LAST() - Returns the last value - MAX() - Returns the largest value - MIN() - Returns the smallest value - SUM() - Returns the sum


Need Your Help

Is it safe to return parameter that was passed by reference?

c# reference return pass-by-reference

I'm usually working with C++, where returning a function argument that was passed as a reference can lead to problems (like a dangling reference or perhaps UB.) Does something similar hold true in ...

Regex allowing white space, characters and numbers

html regex

I am having problem with some regex expression, I need a regex to check whether the field is blank or not, if blank it should show a message "This field is required".

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.