Problem query with min() en first()

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?

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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;

Answer 5

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.