none
Problem query with min() en first() RRS feed

  • Question

  • Hi,

    I have a table called Tbl_Kabelberekening and next you see a snapshot where "Vermogen"=11

    Vermogen Spanning Diameter Lengte Type
    11 400_TN_10 2,5 27 2
    11 400_TN_10 4 44 7
    11 400_TN_10 6 66 3
    11 400_TN_10 10 111 8
    11 400_TN_10 16 178 5
    11 400_TN_10 25 277 1
    11 400_TN_10 35 387 4

    When I use next query to find the right diameter and type ex: power=11, lengte>100 then I get Diameter 10, Type=8 which is OK

    SELECT Tbl_Toestellen.Code, Tbl_Toestellen.LoopNumber, Tbl_Toestellen.Power, Min(Tbl_Kabelberekening.Diameter) AS Diameter, First(Tbl_Kabelberekening.Type) AS Type
    FROM Tbl_Toestellen INNER JOIN Tbl_Kabelberekening ON Tbl_Toestellen.Power = Tbl_Kabelberekening.Vermogen
    WHERE (((Tbl_Kabelberekening.Lengte)>100) AND ((Tbl_Kabelberekening.Spanning)="400_TN_10"))
    GROUP BY Tbl_Toestellen.Code, Tbl_Toestellen.LoopNumber, Tbl_Toestellen.Power;

    when I select the same query whereby I add the field "tbl_Toestellen.Description" I get Diameter 10 and type 5 which is not OK

    SELECT Tbl_Toestellen.Code, Tbl_Toestellen.LoopNumber, Tbl_Toestellen.Description, Tbl_Toestellen.Power, Min(Tbl_Kabelberekening.Diameter) AS Diameter, First(Tbl_Kabelberekening.Type) AS Type
    FROM Tbl_Toestellen INNER JOIN Tbl_Kabelberekening ON Tbl_Toestellen.Power = Tbl_Kabelberekening.Vermogen
    WHERE (((Tbl_Kabelberekening.Lengte)>100) AND ((Tbl_Kabelberekening.Spanning)="400_TN_10"))
    GROUP BY Tbl_Toestellen.Code, Tbl_Toestellen.LoopNumber, Tbl_Toestellen.Description, Tbl_Toestellen.Power;

    When I select the same query whereby I use the criteria Power=11 then it is also OK

    SELECT Tbl_Toestellen.Code, Tbl_Toestellen.LoopNumber, Tbl_Toestellen.Description, Tbl_Toestellen.Power, Min(Tbl_Kabelberekening.Diameter) AS Diameter, First(Tbl_Kabelberekening.Type) AS Type
    FROM Tbl_Toestellen INNER JOIN Tbl_Kabelberekening ON Tbl_Toestellen.Power = Tbl_Kabelberekening.Vermogen
    WHERE (((Tbl_Kabelberekening.Lengte)>100) AND ((Tbl_Kabelberekening.Spanning)="400_TN_10"))
    GROUP BY Tbl_Toestellen.Code, Tbl_Toestellen.LoopNumber, Tbl_Toestellen.Description, Tbl_Toestellen.Power
    HAVING (((Tbl_Toestellen.Power)=11));

    Can somebody explain this?

    Wednesday, May 4, 2016 11:57 AM

Answers

  • Hi all,

    Indeed, the problem is with the grouped by.

    I have made a new query, with 2 subquery fields for Diameter and Type

    Thank you all

    SELECT Tbl_Toestellen.Code, Tbl_Toestellen.LoopNumber, Tbl_Toestellen.Description, Tbl_Toestellen.Power, (select min(Diameter) from tbl_Kabelberekening where Vermogen=  [Power]  AND Spanning='400_TN_10'  AND Lengte>=  [tbl_Toestellen].[Kabellengte]) AS Diameter, (select first(Type) from tbl_Kabelberekening where Vermogen=  [Power]  AND Spanning='400_TN_10'  AND Lengte>=  [tbl_Toestellen].[Kabellengte] AND diameter= [Diameter]) AS Type
    FROM Tbl_Toestellen INNER JOIN Tbl_Vermogen ON Tbl_Toestellen.Power = Tbl_Vermogen.Vermogen;

    Monday, May 9, 2016 7:56 AM

All replies

  • I would suggest you eliminate, temporarily, the Group By clause - as an interim step to better understand the entire record set result of the query (s)... that should give you a more clear insight as to the query results per the criteria used.
    Wednesday, May 4, 2016 1:02 PM
  • Hi Lteu,

    Here In the first query you get a desired result.

    in the second query you add the  "tbl_Toestellen.Description" field. but you have also add that in group by that change the result.

    where in third query you use having clause which is use to filter the records after they have been grouped.

    Here I think you have a issue with GroupBy not with min() and First().

    you can compare the results after removing the tbl_Toestellen.Description field from it.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, May 5, 2016 5:04 AM
    Moderator
  • Adding the description field is throwing off the grouping/totals in your query. In general an approach to pulling additional fields into a working totals query is to make the totals query into a sub query, and 'self-join' it to your table based on fields that uniquely identify each record.  For example, save your query as qryMin and join it to your table like this to pull the description:

    SELECT t.Description, q.* 
    FROM Tbl_Toestellen t inner Join qryMin q ON 
    t.Field1 = q.Field1 AND t.Field2 = q.Field2 AND t.Field3 = q.Field3    

    Also, the FIRST keyword is not reliable unless your query is ordered by an autonumber, record timestamp or another field that consistently indicates the order in which the records were entered.


    Miriam Bizup Access MVP


    • Edited by mbizup MVP Friday, May 6, 2016 10:10 AM
    Friday, May 6, 2016 10:10 AM
  • Hi all,

    Indeed, the problem is with the grouped by.

    I have made a new query, with 2 subquery fields for Diameter and Type

    Thank you all

    SELECT Tbl_Toestellen.Code, Tbl_Toestellen.LoopNumber, Tbl_Toestellen.Description, Tbl_Toestellen.Power, (select min(Diameter) from tbl_Kabelberekening where Vermogen=  [Power]  AND Spanning='400_TN_10'  AND Lengte>=  [tbl_Toestellen].[Kabellengte]) AS Diameter, (select first(Type) from tbl_Kabelberekening where Vermogen=  [Power]  AND Spanning='400_TN_10'  AND Lengte>=  [tbl_Toestellen].[Kabellengte] AND diameter= [Diameter]) AS Type
    FROM Tbl_Toestellen INNER JOIN Tbl_Vermogen ON Tbl_Toestellen.Power = Tbl_Vermogen.Vermogen;

    Monday, May 9, 2016 7:56 AM
  • Hi Lteu,

    its good to hear from you that your issue has been solved.

    Thank you for posting the solution so that if someone having same issue like you can get solution from your post.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, May 9, 2016 9:11 AM
    Moderator