RowSource for Combobox using max function with filter

[simaonobrega]

Dear community,

FrameWork:
I have a table with the following fields: Id, Name, InterventionNumber, CheckPoint, Completed
In a form, I have a combobox that I want to populate with the entries that are obtained using the following criteria:
-First: check the maximum InterventionNumber for each unique Name;
-Second: filter the results of the first operation using the Completed field (it is a checkbox) equals True

Code Used:

Dim qry as String
qry = "Select Id, Name, Max(InterventionNumber) From [table]" & "Where Completed = True " & "Group by Id, Name"
Combobox.RowSource = qry

Problem:
Max function does not work because Id is always different (autonumber) despite the field "Name" possible repetitions.
Question:
How can I apply the filter just taking into account the Name and InterventionNumbers fields but being able to put all the fields that I want in the Combobox.



Thank you very much for your help.

Best regards,
Simão

[ranman256]

you cannot use that many fields to get a MAX.
it usu takes 2 queries, the 1st to get the max, then 2nd to pull all data using the ID from q1:

Q1: select id, MAX(interventn#) from table
then
Q2: select * from table, Q1 where Q1.id=table.ID

use Q2.

[John_G]

How can I apply the filter just taking into account the Name and InterventionNumbers fields but being able to put all the fields that I want in the Combobox.

Since the ID is an autonumber, it is always different as you noted, so your Group By is meaningless.

-First: check the maximum InterventionNumber for each unique Name;
-Second: filter the results of the first operation using the Completed field (it is a checkbox) equals True

From those criteria for the combo box, you don't need the ID at all:

Dim qry as String
qry = "Select Name, Max(InterventionNumber) From [table]" & "Where Completed = True " & "Group by Name"
Combobox.RowSource = qry

PS: "Name" is not a good choice for a field name - it is a reserved word in Access, and while your code will sometimes work, using "Name" as a field name will often confuse Access.

[simaonobrega]

ranman256
Thank you for your reply.
I will try to implement the algorithm given by you.

John_G
Thank you for your reply.
The names given to the fields represent only a debug code.
The Id is used to apply the filter when an edit is done by the user, so it is a parameter that I must have in my Combobox

[John_G]

Given those requirements. I think you do need a two-query solution.

check the maximum InterventionNumber for each unique Name:

Q1: Select Name, Max(InterventiionNumber) From Table Group by Name

Q2: Select * from table, Q1 where Q1!MaxInterventionNumber = table!Interventionnumber where Completed = True

Notice there is no group by in Q2 - it is not needed now, since Q1 limits Q2 to only one record for each name - the record containing the maximum InterventionNumber.

Q2 will be the record source for your combo box, and it does contain the ID as required. In Q2, you should change Select * to just the 3 (?) fields you want from Table.

[simaonobrega]

John_G
Thank you for your reply.
When implementing the code sugested, I am getting an empty combobox. Here is the code:

Dim qry1 As String
Dim qry2 As String
qry1 = "Select ValveName, Max(InterventionNumber) From [tbValve] Group by ValveName"
qry2 = "Select * from [tbValve], qry1 Where qry1!Max(InterventionNumber) = tbValve!InterventionNumber Where Completed = True"
cbValve.RowSource = qry2

Do you Know why is this happening?

Regards

[June7]

Can't have two WHERE clauses. Probably just a typo oversight by John. Maybe:

qry2 = "Select {some field name(s) here} From [tbValve], qry1 Where qry1!Max(InterventionNumber) = tbValve!InterventionNumber AND Completed = True;"

or

qry2 = "Select {some field name(s) here} From [tbValve] INNER JOIN qry1 ON qry1!Max(InterventionNumber) = tbValve!InterventionNumber Where Completed = True;"

[simaonobrega]

June7,

Thank you very much for your reply.
Unfortunately both lines of code produce blank combobox. Even when the "Where Completed = True" condition is out of the code line.
By the way, why in qry2 it is necessary to state qry1!Max(InterventionNumber). Shouldn't be enough to use qry1!InterventionNumber?

[June7]

What do you mean by 'empty' combobox - the dropdown list has no items?

Maybe:

qry1 = "Select ValveName, Max(InterventionNumber) AS MaxIntNum From [tbValve] Group by ValveName"

qry2 = "Select {some field name(s) here} From [tbValve], qry1 Where qry1!MaxIntNum = tbValve!InterventionNumber AND Completed = True;"

[simaonobrega]

June7

That's what I meant, the dropdown list has no items.
Thank you once again for the two last solutions. Unfortunately, it is not working.
qry1 is working perfectly, this is, the maximum for each ValveName is obtained.
The problem is with query number 2.

[June7]

Well, post your exact attempt that is failing.

Did you use Access query builder to build and test the query syntax?

Wait a minute - doh! Qry1 must be a saved query object, not just a string in VBA to set a combobox RowSource.

That or nest it into qry2 statement

qry2 = "Select {some field name(s) here} From [tbValve], (Select ValveName, Max(InterventionNumber) AS MaxIntNum From [tbValve] Group by ValveName) AS qry1 Where qry1!MaxIntNum = tbValve!InterventionNumber AND Completed = True;"

or


qry2 = "Select {some field name(s) here} From [tbValve], (" & qry1 & ") AS qry1 Where qry1!MaxIntNum = tbValve!InterventionNumber AND Completed = True;"

[simaonobrega]

Thank you for your reply. Here is a simple debug version with the implementation of the code line.

Something is wrong. The code should be applied to obtain the following results:
First criteria should select: ID 2, 4, 5, 6 (max for each ValveName)
Second Criteria should select: ID 4, 6 (from the results of the first criteria, the completed are chosen)

As sugested, maybe using the initial code with the declaration that qry1 is a query object will solve the problem. When Dim qry1 as QueryDef is used a copile error "Invalid use of property" occur at the select line.

[John_G]

I have it working. It was easiest to create and save the two queries outside of the form, and just use "Query2" as the row source for the combo box.

One thing you forgot to mention is that the maximum intervention number can be the same for different valve names. That's not a problem - it just means that Query2 has to use two fields (maxInter AND valve name) to join Query1 and tblValve.

Query1: SELECT tbValve.ValveName, Max(tbValve.InterventionNumber) AS MaxIntNum FROM tbValve GROUP BY tbValve.ValveName;


Query2: SELECT * FROM query1 INNER JOIN tbValve ON (query1.ValveName = tbValve.ValveName) AND (query1.MaxIntNum = tbValve.InterventionNumber)
WHERE (((tbValve.Completed)=True));

I used the query design grid to do the joins, so the SQL shows an INNER JOIN on the two fields instead of putting the conditions into the WHERE clause, but it does the same thing.

When I tested the form, the combo had ID's 4 and 6, as expected.

The modified Example.accdb is attached.

[simaonobrega]

Hello John_G,

Thank you very much for the effort!
I have understood the solution given by you and is working perfectly. I will now just copy the SQL lines to the VBA code.
Thank you once again.

Best regards