Set up "WHERE" criteria on the fly

[mdsoren]

HELP!!! I have been trying find a way to create a SELECT query at runtime based on the user selecting values from five combo boxes. They must select at least one, but may select any combination or even all five. The data comes from a hierarchy of tables. My table structure can be one, two or three tables deep depending on what selection criteria they elect and may be comparing values in as many as six tables looking for a match. The SELECTed data always comes from the same two tables and will be the same fields no matter what search criteria they have elected to filter on.

Does anyone have an idea how to go about setting up this query??? I have tried using UNIONs, but the result ends up like an OR query rather than an AND showing any record that matches even one of the criteria.

I NEED HELP - PULL-EZE!

TIA,
MDS

 

[JeremyNYC]

It's going to be really hard to give you much advice on this without knowing a LOT more. But I thik it will be easier for you to do than to describe in the detail it would require. Try doing it one combo box at a time. If you do it that way, you may end up doing a little extra work that has to be undone, but you'll get a really good sense of why it works, and how to generalize your solutions.

On the other hand, if you want to post back here with more detail, we may be able to help. If you're going to do this, we'll need to know what the tables are and what the relationships are and where you want to use OR and where you want to use AND. Spend some time composing the note, as it's a LOT of information. My guess is that when you're done with this process you'll have some really good ideas for how to approach it yourself.

Jeremy =============
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[mdsoren]

Jeremy,

I will try and give you the gist of what I am working with...

Table structure - tblCorp is a table of corporations with an specific ID for ea record along with a little company specific data, e.g. responsible attorney, primary contact, corporation type (for-profit, not-for-profit, etc). Tied to the corp table are multiple other tables. tblOrganization, which contains a field which ties the records back to the tblCorp as well as fields telling what states the corporations are registered in, etc. The tblCorp has a one-to-many relation to tblOrganization. tblOfficers which is simply a list of officers of the corporation, again tied back to tblCorp by a foreignkey like tblOrganization and likewise tblCorp has a one-to-many relation to tblOfficers. And one more example, tblCapital, which just like the others is tied back to tblCorp and contains the classes of stock each corporation has issued (Class A Preferred, Class B Common, etc). Tied to tblCapital is a table of the actual certificates issued, tblCertificates. tblCapital is tied to tblCertificates in a one-to-many relation as each class of stock can have many certs issued.

Now the user has the capability to search for (1) names - which may be a primary contact in tblCorp, an officer in tblOfficer or a certificate holder in tblCertificates, or (2) corporations within a specific state - state information is maintained in tblOrganization, or (3) the responsible attorney handling the coprorations files - which is maintained in tblCorp. There are several other tables and search criteria, but this gives you the bulk of it.

The data to be returned based on the search criteria is always the same and is as follows: Corporation name and responsible attorney (both in tblCorp) and state location & whether it is "Organized" in said state or just "doing business" there, called "Authorized" - this is indicated by a single character ("O" or "A&quot, both of these fields are in tblOrganziation.

The choices the user can select from are in combo boxes, so I knw exactly what they will be searching for, so I don't have to use LIKE or any partial strings. And I have written queries which search each individual criteria by itself. My problem remains... how do you combine the criteria into a single search. For example, the user wants to find all corporations in Iowa, whose repsonsible attorney is Richard Davidson and have a stock holder named John Bush? I can find all the corps in Iowa, I can find all the corps that Richard is the attorney for and I can find all the corps where John Bush is a stock holder, but how do I find any possible matches that meet ALL three requirements without knowing in advance that the user is even going to select all three of these requirements. Remember there are actually 5 criteria they can search for and which involve a couple more tables. In most cases (except stock holders) the search criteria lies in a table which subordinate to tblCorp and has a one-to-many relation. Stock ownership is tougher as it two layers down from tblCorp as described above.

Is that enough (or too much!) information? I can post the individual queries as well if that will help...

If intIndividual <> 0 Then
        strSQL1 = &quot;SELECT DISTINCT tblCorp.ID, tblOrganization.OrganizedAuthorized, tblOrganization.StateOorA, EntityName, RespAtty FROM (tblCorp LEFT JOIN tblOrganization ON tblCorp.ID = tblOrganization.CorpForeignKey) LEFT JOIN tblOfficers ON (tblCorp.ID = tblOfficers.CorpForeignKey) WHERE (tblCorp.PrimaryContact = &quot; & intIndividual & &quot; OR tblCorp.SecondaryContact = &quot; & intIndividual & &quot; OR tblOfficers.Name = &quot; & intIndividual & &quot;) AND tblOrganization.OrganizedAuthorized = 'O' UNION SELECT DISTINCT tblCorp.ID, tblOrganization.OrganizedAuthorized, tblOrganization.StateOorA, EntityName, RespAtty FROM ((tblCorp LEFT JOIN tblOrganization ON tblCorp.ID = tblOrganization.CorpForeignKey) LEFT JOIN tblCapital ON tblCorp.ID = tblCapital.CorpForeignKey) LEFT JOIN tblCertificates ON tblCapital.ID = tblCertificates.CapForeignKey WHERE (tblCertificates.SecurityHolder = &quot; & intIndividual & &quot; AND tblCertificates.Outstanding = 'Yes') AND &quot;tblOrganization.OrganizedAuthorized = 'O' &quot;
    End If

    If intAttorney <> 0 Then
        strSQL2 = &quot;SELECT DISTINCT tblCorp.ID, tblOrganization.StateOorA, tblOrganization.OrganizedAuthorized, EntityName, RespAtty FROM  tblCorp, tblOrganization WHERE tblCorp.ResponsibleAttorney = &quot; & intAttorney & &quot; AND tblOrganization.CorpForeignKey = tblCorp.ID AND 
tblOrganization.OrganizedAuthorized = 'O' &quot;
    End If
    
    If strState <> &quot;&quot; Then
        strSQL3 = &quot;SELECT DISTINCT tblCorp.ID, tblOrganization.OrganizedAuthorized, tblOrganization.StateOorA, EntityName, RespAtty 
FROM tblCorp RIGHT JOIN tblOrganization ON (tblCorp.ID = tblOrganization.CorpForeignKey) WHERE tblOrganization.StateOorA = '&quot; & strState & &quot;' &quot;
    End If
    
    If strEtype <> &quot;&quot; Then
        strSQL4 = &quot;SELECT DISTINCT tblCorp.ID, tblOrganization.StateOorA, tblOrganization.OrganizedAuthorized, EntityName, RespAtty 
FROM tblCorp, tblOrganization WHERE tblCorp.EntityType = '&quot; & strEtype & &quot;' AND tblOrganization.OrganizedAuthorized = 'O' AND tblOrganization.CorpForeignKey = tblCorp.ID&quot;
    End If
    
    If intAcctFirm <> 0 Then
        strSQL5 = &quot;SELECT DISTINCT tblCorp.ID, tblOrganization.StateOorA, tblOrganization.OrganizedAuthorized, EntityName, RespAtty FROM tblCorp, tblAccounting, tblOrganization WHERE tblAccounting.AccountingFirm = &quot; & intAcctFirm & &quot; AND tblAccounting.CorpForeignKey = tblCorp.ID AND tblOrganization.CorpForeignKey = tblCorp.ID AND tblOrganization.OrganizedAuthorized = 'O' &quot;
    End If

 

[JeremyNYC]

Well, it's mighty close to too much information <G>, but I can see some general things that will help you out.

For one thing, it sounds like you have people stored in several different tables. If that's the case, it will be better to store all of the people in one table and then you can store PersonID in the other tables. It will make maintenance a bunch easier, though it will mean rewriting all this sql.

When I said do the combo boxes one at a time, I wasn't very clear. What I meant is first do one. Then get the system working for two, allowing for choices to be made in one, the other, or both. Then add another one, allowing for all the possibilities.

You'll end up doing a bunch of concatenating. Here's an example, using bogus controls and tables (totally untested aircode):

strSql = &quot;SELECT CarID, MakeName, ColorName, Year&quot; _
& &quot; FROM (tblCar INNER JOIN tblColor ON tblCar.ColorID = tblColor.ColorID)&quot; _
& &quot; INNER JOIN tblMake ON tblCar.MakeID = tblMake.MakeID&quot;

if nz(me!cmbMake) > 0 then
strWhere = &quot; AND tblCar.MakeID = &quot; & me!cmbMake
end if

if nz(me!cmbColor) > 0 then
strWhere = &quot; AND tblCar.Color = &quot; & me!cmbColor
end if

if nz(me!txtYear) > 0 then
strWhere = &quot; AND tblYear = &quot; & me!txtYear
end if

if len(strwhere) > 0 then
strsql = strsql & mid(strWhere, 5)
end if

What I've done here is to isolate the parts that will stay the same (the SELECT and FROM clauses) from the rest of it, so that I won't have to maintain multiple copies of that in code. The other nifty trick is to put the &quot; AND &quot; at the start of each clause, so that they come together properly and it's always OK to lop off the first 4 characters when I concatenate.

Clearly your situation is more complex than this. You'll have to be clear with the users (probably on screen) about what's being ANDed and what's being ORed. You'll have to make a decision about whether or not it's too slow to always include all of the tables when you do your select, even if the user is not filtering on all of the tables. That's certainly the way I would go at first, as it will be most straightforward. But if it's really slow, you may not want to put the users through all that wait when it's not necessary.

How about if you give this strategy a go and get back to us with where you've gotten. But start with two items and work your way up.

Jeremy =============
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[mdsoren]

Thanx Jeremy.

Actually, I do have the names all stored in one table and ID's used in all the subsequent tables, I just abbrev'd my code to eliminate those references as I know how to get the name if the ID is selcted.

I like the idea of putting the &quot;AND&quot; at the beginning and then just lopping off the first 4 char's.

I truly appreciate the assist and will let you know how it goes from here. BIG thanx again!

MDS