Using DMin in Query to create a record set

[Danick]

Trying to create a recordset via a query where all records shown with the lowest item number record. I've tried this, but it's not working.

DMin("ItemNo","Findings",[Findings].[ItemNo])

So for example, I have this in the table

Any ideas how to fix it?

 

[plog]

First, your DMIN is only part of the code, I'd really need to know the entire code to really help you. With that said, your DMin is essentially constant--it is set to find the lowest ItemNo in your table. Not the lowest ItemNo among a subset of records, but the lowest ItemNo in the entire table. So, its going to find 01 and only bring back those with ItemNo=01. You need to fix your Dmin so that it takes into account the subset of records it should be operating on--not the entire table.

Again though, I don't know if Dmin is the correct way to achieve this. I think a correlated subquery might be more efficient.

 

[Danick]

First, your DMIN is only part of the code, I'd really need to know the entire code to really help you. With that said, your DMin is essentially constant--it is set to find the lowest ItemNo in your table. Not the lowest ItemNo among a subset of records, but the lowest ItemNo in the entire table. So, its going to find 01 and only bring back those with ItemNo=01. You need to fix your Dmin so that it takes into account the subset of records it should be operating on--not the entire table.

Again though, I don't know if Dmin is the correct way to achieve this. I think a correlated subquery might be more efficient.

Thanks for pointing me in the right direction. I managed to get this to work using a correlated subquery as explained here:

SQL Correlated Subqueries - GeeksforGeeks

Your All-in-One Learning Portal: GeeksforGeeks is a comprehensive educational platform that empowers learners across domains-spanning computer science and programming, school education, upskilling, commerce, software tools, competitive exams, and more.

www.geeksforgeeks.org