How to Search Multiple One-to-Many Queries and Filter Results on a Form?

[rawlstonb]

frmSearch is the brick wall I ran in to... the only examples I can find for search forms are just searching one table or one query...



Ideally, I would like the search box to search multiple one-to-many queries as you type... grab the SyncPairIDs after it filters them, condense the many records to 1, then filter the SyncPairID in list box

The combo boxes should all individually filter box as well, but that seems easier since the records will match them 1:1...

qrySearchMoves, qrySearchPassives, and qrySearchThemes are being used to generate reports on the navigation control. The list box is the control to select which SyncPairID to display in the navigation control.

It could use a continuous form in the Detail panel if needed, I was playing around with some of Allen Brown's search code for a bit... broke it, and got lost.... I also tried doing it with query design... broke it, and got lost.... been stuck on this for about a week

[Micron]

Very sketchy on the details. I downloaded but think I'll pass. No idea what to do to replicate the issue let alone solve it but mainly, the form needs an entire re-design to fit on my laptop screen. Can't even scroll to what can't be seen as scrollbars have been turned off. Someone will likely come along shortly. In the meantime, consider posting some instructions for others.

EDIT - scrollbars seem to be enabled but for some reason, are not showing when they apparently should.

[rawlstonb]

Very sketchy on the details. I downloaded but think I'll pass. No idea what to do to replicate the issue let alone solve it but mainly, the form needs an entire re-design to fit on my laptop screen. Can't even scroll to what can't be seen as scrollbars have been turned off. Someone will likely come along shortly. In the meantime, consider posting some instructions for others.

EDIT - scrollbars seem to be enabled but for some reason, are not showing when they apparently should.

I wasn't too concerned with aesthetics at this stage, just functionality... if you do want to take a stab at it, you can give me the resolution you're running and I can resize it to fit? I'm working off a 1440p monitor, so it's not terribly surprising...

qryMoves, qryThemes, and qryPassives all hold the values I'd like to filter from... everything on all the tables is tied back to the SyncPairID in tblSyncPair

Goal: To type in the search box and have the listbox filter through SyncPairID as I type... the search box should be searching all the connected values in qryMoves, qryThemes, and qryPassives.... the combo boxes should also be able to OnUpdate dynamically filter the listbox individually...

Examples:

If "sand" is typed in the Search box... then it should search all the queries for any mention of *sand*, reduce those records to unique sync pair IDs, and update the list box for matches

If "Bug" is selected in cboType, only Bug types should show up... if "Strike" is selected in cboRole, only Strike roles should show up... if Bug and Strike is selected in cboType and cboRole, then only Bug AND Strike SyncPairIDs show up in the list box.... etc...

Is that clear enough to make sense for what I'm looking for?

For all intents and purposes.... you can disregard the footer entirely as it's not connected to the issue.... that part is working fine

[Micron]

I'm using 1366x768 with a 15.5" (diagonal) screen. Yours isn't the only one I've had this issue with lately - there have been several others and it makes it difficult to help when a form is (e.g.) 16" wide and everything about it is a barrier (like no scrolling, no control box, no border, can't see the user added buttons and so on). I'll remove the footer elements and take another look based on your recent comments.

[Micron]

If "Bug" is selected in cboType, only Bug types should show up

Show up where? In the listbox that already (maybe) has a list based on "*sand*"? You've got to be specific/clear.

If you're looking for a query solution, edit the 3 queries (because they have no PK field from the 'primary' table. I chose SyncPairID field for each). Then join all on SyncPairID in a 4th query. For every field you want to search on you'll need criteria in a separate row. For this test I put Like "*sand*" in every row. You can open the 4th query after the changes and dump in the following sql and test:

SELECT DISTINCT qryMoves.SyncPairID, qryMoves.Moves, qryPassives.Pokemon, qryPassives.Passive, qryThemes.Pokemon, qryThemes.Trainer
FROM qryMoves INNER JOIN (qryPassives INNER JOIN qryThemes ON qryPassives.SyncPairID = qryThemes.SyncPairID) ON qryMoves.SyncPairID = qryPassives.SyncPairID
WHERE (((qryMoves.Moves) Like "*sand*")) OR (((qryPassives.Pokemon) Like "*sand*")) OR (((qryPassives.Passive) Like "*sand*")) OR (((qryThemes.Pokemon) Like "*sand*") AND ((qryThemes.Trainer) Like "*sand*"));

You should get about 62 unique records based on your posted db. However, you will get field results without *sand* because it is an OR situation IF you are going to try to search multiple queries at once for the same thing. I still don't know if that's the goal as you've only stated that 3 queries need to be searched. If not, all of the above is pointless and what you likely need is a UNION query so that you append the search results of each individual query into one.

[rawlstonb]

per the example, yes... if there is text in the searchbox, then the combo boxes would further filter those return results...

I don't particularly care if it's done with a query or VBA, so long as it acts like it's supposed to.... let me plug this query in and see if it's doing what it's supposed to do

[rawlstonb]

I think I get it… so in each column I want to search on each query would be:

Like “*” & Forms!frmSearch!txtSearch & “*”

… then make a union query for the three queries with a unique SyncPairID?

[Micron]

If you put it all on one criteria row you will be asking for
WHERE Fld1 Like "*" & Forms!frmSearch!txtSearch & "" AND Fld2 Like "*" & Forms!frmSearch!txtSearch & "" AND Fld3 Like "*" & Forms!frmSearch!txtSearch & ""...
You will probably get nothing because of the AND

If you put each reference on a different row you will be asking for
WHERE Fld1 Like "*" & Forms!frmSearch!txtSearch & "" OR Fld2 Like "*" & Forms!frmSearch!txtSearch & "" OR Fld3 Like "*" & Forms!frmSearch!txtSearch & ""...
You will almost never get unique ids as per my last post.

So I don't really see you getting exactly what you want with what you have to work with in terms of your tables and queries. What I was getting at in my last post is that as long as you're going to search more than one field for the same value in any query you will have one of those issues. If you search only one field you should be OK, but I guess you're not doing that.

Even if you used a code solution, I don't see this working based on my understanding. If you look at the records for sync 35 (you did do that, right?) generated by the sql I posted, 4 fields have all the same values in 5 records yet these records are unique because of various Moves field values. So how would you ever get unique sync values when you have 5 records with the same value (e.g. 35) because of the different values in Moves?

SyncPairID	Moves	qryPassives.Pokemon	Passive	qryThemes.Pokemon	Trainer
35	Earthquake	Garchomp	Surging Sand 5	Garchomp	Cynthia
35	Earthquake of Ancient Lore	Garchomp	Surging Sand 5	Garchomp	Cynthia
35	Stomping Tantrum	Garchomp	Surging Sand 5	Garchomp	Cynthia
35	This Match is Too Fun!	Garchomp	Surging Sand 5	Garchomp	Cynthia
35	X Attack	Garchomp	Surging Sand 5	Garchomp	Cynthia

Took a quick look at your relationships and while I don't understand the activity behind the db I'll still say I don't see the point of junction tables with only foreign key fields. Look at Passives and its junction. PassiveID is in both tables (in junction as PassiveID_fk), so the values would be the same in each table. That leaves only SyncPairID in the junction and nothing else. Might as well just have SyncPairIDfk in Passives table and forget about the junction?

[rawlstonb]

The passives table is a normalized table of all possible passives… a sync pair ID can and likely will have multiple passives… the same passives might be used on multiple sync pair IDs… the junction table is there to solve the many to many relationship issue…

I did look at the one you posted and, assuming that was searching correctly, all that would need to be done is take the active sync pair ID of the search results, combine them all, then filter out the duplicate Sync Pair IDs… I don’t need to carry over any data from the queries… I just need it to determine which Pair IDs are relevant to the search… it will search, more or less, all the text fields in the queries for relevance

Make sense?

[Micron]

Sounds like you've got your path to a solution then. Not sure how you filter out the dupes; I'm not seeing anything that points to which 2, 3 or 4 of those 5 "35" id values that you'd drop.

[rawlstonb]

Was planning to run a separate union query that just pulls the syncpairIDs of the three queries as unique… is it not that simple? I’ll work on it tomorrow

[rawlstonb]

Sounds like you've got your path to a solution then. Not sure how you filter out the dupes; I'm not seeing anything that points to which 2, 3 or 4 of those 5 "35" id values that you'd drop.

It worked... thank you for pointing me to union queries...

So what I did, in case anybody is curious.... I inserted:

Like "*" & Forms!frmSearch!txtSearchAll & "*"

into each relevant text field on cascading lines to give the OR clause in design mode in qryMoves, qryPassives, and qryThemes... I then made qrySearchUnion to pull all the SyncPairIDs...

SELECT SyncPairID FROM qryMoves
UNION
SELECT SyncPairID FROM qryPassives
UNION
SELECT SyncPairID FROM qryThemes;

That filtered all the return values into unique Sync Pair IDs... I created one more query to pull all the relevant display data for the list box from the tables and used qrySearchUnion.SyncPairID to filter it... may not be the most elegant solution but it does exactly what it's supposed to do. Thank you for the gentle nudge to the solution

[Micron]

Thanks to you for posting your solution. Glad I was able to help in some small way.
I guess you can mark this thread as solved?