# 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