Solved Listbox search not working with aggregated query

[elannesse]

Hi there,

Long-time user of these forums (previously fluffyozzy). I am stuck with something and hoping someone can point me in the right direction. I have a listbox with a number of fields and a search box on a form. I have done this before and always worked but this time it is not working and I am thinking maybe it is something to do with the underlying query behind the search. Listbox displays the records fine.

Query 1: This shows projects and people associated with it, with multiple rows for each project and the personID
SELECT tblLinkProjects.PersonRoleLinkID, tblLinkProjects.ProjectID, tblProjects.ProjectGroup, tblLinkProjects.PersonID, tblLinkProjects.PersonRoleID, tblLinkProjects.DegreeID, tblLinkProjects.PersonActive, tblProjects.ProjectTitle, tblProjects.NHSRef, tblProjects.IRASRef, tblProjects.CurrentStatus, tblProjects.CurrentStatusDate, DLookUp("ProjectStatus","tblProjectStatus","ProjectStatusID = " & [CurrentStatus]) AS Status, tblProjects.ProjectEndDate, qryPeople.PersonName
FROM tblProjects INNER JOIN (tblLinkProjects INNER JOIN qryPeople ON tblLinkProjects.PersonID = qryPeople.PersonID) ON tblProjects.ProjectID = tblLinkProjects.ProjectID;

Query 2: This uses the previous query and gets the records on a single row per project and concatenates people associated with the project
SELECT p.ProjectID, Max(p.ProjectGroup) AS MaxOfProjectGroup, Max(p.PersonID) AS PersonID, Max(p.PersonRoleID) AS PersonRoleID, Max(p.ProjectTitle) AS MaxOfProjectTitle, Max(p.NHSRef) AS MaxOfNHSRef, Max(p.IRASRef) AS MaxOfIRASRef, Max(p.Status) AS MaxOfStatus, Max(p.CurrentStatusDate) AS MaxOfCurrentStatusDate, Max(p.PersonName) AS PersonName, SimpleCSV("SELECT PersonName FROM qryProjectsMainPrep WHERE ProjectID = " & [p].[ProjectID] & " AND [PersonRoleID] = 2") AS Students, SimpleCSV("SELECT PersonName FROM qryProjectsMainPrep WHERE ProjectID = " & p.ProjectID & " AND [PersonRoleID] = 3") AS Staff, Max(p.ProjectEndDate) AS MaxOfProjectEndDate
FROM qryProjectsMainPrep AS p
GROUP BY p.ProjectID
HAVING (((Max(p.PersonActive))=Yes));

Listbox: This is based on the Query 2

Search textbox: This is based on a copy of Query 2 to "reduce as you type" sort of search within the listbox. The code behind it:

Private Sub ProjectsSearchBox_Change()

Dim strSQL As String
Dim searchText As String

searchText = Replace(Me.ProjectsSearchBox.Text, "'", "''") ' Escape single quotes

' Build the dynamic SQL query with wildcard search for each field
strSQL = "SELECT * FROM qryProjectsMainSearch " & _
"WHERE ProjectTitle LIKE '*" & searchText & "*' OR " & _
"NHSRef LIKE '*" & searchText & "*' OR " & _
"IRASRef LIKE '*" & searchText & "*' OR " & _
"CurrentStatusDate LIKE '*" & searchText & "*' OR " & _
"Students LIKE '*" & searchText & "*' OR " & _
"Staff LIKE '*" & searchText & "*' OR " & _
"ProjectEndDate LIKE '*" & searchText & "*' " & _
"GROUP BY ProjectID " & _
"HAVING Max(PersonActive) = Yes " & _
"ORDER BY ProjectEndDate"

' Set the listbox RowSource
Me.ProjectsListBox.RowSource = strSQL

It is not working and I can't figure out why, no error message produced. The listbox just goes blank and stays that way. Can anyone give me some advice please?

 

[June7]

GROUP BY query is not going to work with wildcard for fields in SELECT clause.

 

[ebs17]

SELECT *
...
GROUP BY ProjectID

It's even more extreme. In grouped queries, every field used anywhere must be either grouped or aggregated or the content must be constant.
The only exceptions are expressions in the FROM part and in the WHERE part, because their operations take place before grouping.
With the Wildcard * you have no control over what you bring in.

But I myself have major concerns about the previous queries. There's a lot to discuss in there.

 

[elannesse]

@ebs17 regarding the queries, yes, me too. I tried many things to concatenate names into a row per project and this was the only thing that worked to get one ProjectID and group the people in concatenated format into one row just for the purpose of displaying them in a listbox. Not sure how else to do it, open to suggestions

 

[arnelgp]

not tested:

Dim sql As String
sql = sql & "SELECT p.ProjectID, Max(p.ProjectGroup) AS MaxOfProjectGroup, "
sql = sql & "Max(p.PersonID) AS PersonID, Max(p.PersonRoleID) AS PersonRoleID, "
sql = sql & "Max(p.ProjectTitle) AS MaxOfProjectTitle, Max(p.NHSRef) AS MaxOfNHSRef, "
sql = sql & "Max(p.IRASRef) AS MaxOfIRASRef, Max(p.Status) AS MaxOfStatus, "
sql = sql & "Max(p.CurrentStatusDate) AS MaxOfCurrentStatusDate, "
sql = sql & "Max(p.PersonName) AS PersonName, "
sql = sql & "SimpleCSV(""SELECT PersonName FROM qryProjectsMainPrep WHERE ProjectID = "" & [p].[ProjectID] & "" AND [PersonRoleID] = 2"") AS Students, "
sql = sql & "SimpleCSV(""SELECT PersonName FROM qryProjectsMainPrep WHERE ProjectID = "" & p.ProjectID & "" AND [PersonRoleID] = 3"") AS Staff,"
sql = sql & "Max(p.ProjectEndDate) AS MaxOfProjectEndDate "
sql = sql & "FROM qryProjectsMainPrep AS p "
sql = sql & "WHERE ProjectTitle LIKE '*" & searchText & "*' OR "
sql = sql & "NHSRef LIKE '*" & searchText & "*' OR "
sql = sql & "IRASRef LIKE '*" & searchText & "*' OR "
sql = sql & "CurrentStatusDate LIKE '*" & searchText & "*' OR "
sql = sql & "Students LIKE '*" & searchText & "*' OR "
sql = sql & "Staff LIKE '*" & searchText & "*' OR "
sql = sql & "ProjectEndDate LIKE '*" & searchText & "*' "
sql = sql & "GROUP BY ProjectID "
sql = sql & "HAVING Max(PersonActive) = Yes "
sql = sql & "ORDER BY ProjectEndDate"

' Set the listbox RowSource
Me.ProjectsListBox.RowSource = sql

 

[elannesse]

I think I may have resolved this. I had the source of the search box query as a copy of Query 2, instead of using it as source and making it into a SELECT type query rather than aggregated. It seems to be working now. Thank you all for reading and comments (keeping that code in my library of code snippets arnelgp, thank you). I will close this for now. Have a lot of problems with this database I'm sure I'll be back

 

[ebs17]

I would derive a suggestion
- from knowledge of the hopefully well thought-out database schema,
- from knowing the desired result,
- probably also from knowledge of existing data
... actually with the help of a demo database with a few but meaningful records and the function used.

Revising your queries is far too tedious and not very profitable.

Obvious problems without completeness:
1) The first query starts with the unknown query qryPeople. An unknown at the beginning makes everything that follows questionable.
2)

FROM tblProjects INNER JOIN (tblLinkProjects INNER JOIN qryPeople ON tblLinkProjects.PersonID = qryPeople.PersonID ...

In the first query, personal names are multiplied using the JOIN in order to reduce them again by grouping in the next query (two steps forward and two steps back).
3) I have great doubts as to whether the MAX produces correct records for all values in the second query.

 

[elannesse]

Yeah, I suppose I haven't explained the database schema my question. The first query pulls records from tblProjects, qryPeople and a link table that connects the two. qryPeople is a query to combine people's first, middle and last names into one column (using a function to format it properly). So, it is not an unknown per se.

I am never sure if aggregate functions gives the correct results to be honest, I always feel nervous about using them. So far, upon checking a bunch of the data, the second query seems to pull the correct information.