Getting Max from SELECT Query

[kt1978]

Hi

I currently have 2 queries..

Query 1 select statement getting count of field

Query 2 getting max of Query 1

Instead of the queries I want to do this in vba with a recordset

  ' Select from first query
  sql = "SELECT tblOriginal.ROUTE, Count(tblOriginal.ROUTE) AS CountOfROUTE " & _
        "FROM tblOriginal " & _
        "GROUP BY tblOriginal.ROUTE " & _
        "ORDER BY Count(tblOriginal.ROUTE) DESC"

  ' Select max from result of first query
  sql2 = "SELECT Max(qryRouteCount.CountOfROUTE) AS MaxOfCountOfROUTE " & _
        "FROM qryRouteCount;"

The slq works fine for the first Query, I could then do a iMax = rs("CountOfROUTE") to get the max value as they are ordered.

but how can I write sql 2 so that it selects the Max from the Query 1 select statement.

Hope this makes sens

 

[namliam]

You cant, or not the way you are used to with query objects... you will have to do something like:

  sql2 = "SELECT Max(qryRouteCount.CountOfROUTE) AS MaxOfCountOfROUTE " & _
        "FROM ( " & _
        "SELECT tblOriginal.ROUTE, Count(tblOriginal.ROUTE) AS CountOfROUTE " & _
        "FROM tblOriginal " & _
        "GROUP BY tblOriginal.ROUTE " & _
        " ) as x "

 

[Solo712]

You cant, or not the way you are used to with query objects... you will have to do something like:

  sql2 = "SELECT Max(qryRouteCount.CountOfROUTE) AS MaxOfCountOfROUTE " & _
        "FROM ( " & _
        "SELECT tblOriginal.ROUTE, Count(tblOriginal.ROUTE) AS CountOfROUTE " & _
        "FROM tblOriginal " & _
        "GROUP BY tblOriginal.ROUTE " & _
        " ) as x "

SELECT TOP 1 in "sql" wouldn't work ?

Best,
Jiri

 

[kt1978]

Hi

Just realised I didn't post back on here about this...

namliam, had to change the reference to 'x' at the end to be the route count, but other than that it was perfect.

sql = "SELECT Max(RouteCount.CountOfROUTE) AS MaxRoutes " & _
         "FROM ( " & _
            "SELECT tblOriginal.ROUTE, Count(tblOriginal.ROUTE) AS CountOfROUTE " & _
            "FROM tblOriginal " & _
            "GROUP BY tblOriginal.ROUTE " & _
            " ) as RouteCount;"

 

[namliam]

Thank you for posting back, yeah Obvious mistake on the "as X" part

Is what you get for posting aircode