Help with UNION

[mdsoren]

I have an app which allows the user to create their own search criteria (by selecting from any or all of 5 fields). The selections they can make come from values in combo boxes, so they can only search for data that pre-exists.

I have built an individual SELECT query for each searchable field. By testing to see if they have selected a value I build a UNION of these individual SELECT queries.

My problem is I only want records returned that match ALL the criteria. The way it works right now, it returns a record if it matches ANY single element of the criteria.

I am very inexperienced at building complex queries and this is the first time I have used a UNION. There's probably a better way to do this, but I am afraid I don't know what that would be.

Right now it is treating each element in the search criteria like an OR, whereas I need it to treat each element as an AND.

Can some one help???

TIA,
MDS

 

[RiverGuy]

So the app has some VB or VBA coding? That would be easier I think.

I'm thinking you code all of the fields into your SQL string. If the user does not select the field, you use a '*'.

Say they chose fields 1 and 2 only. Would be like:


SELECT * FROM Whatever WHERE Field1 = criteria AND Field2 = criteria AND Field3 LIKE * AND Field4 LIKE *

 

[mdsoren]

That might work... but I am pulling the data from 6 different tables, in at least two cases using "parent-child" relationships between the tables (in at least one of those cases that relationship extends two levels deep, a sub-table and a sub-sub-table). I have used LEFT JOINS on some tables and in others I just use the WHERE clause.

The short of it is, it's a mess. Here are the 5 SELECTS I use to build the UNION, could they be re-written to accomplish what you suggest as one single SELECT???

strSQL1 = SELECT DISTINCT tblCorp.ID, tblOrganization.OrganizedAuthorized, tblOrganization.StateOorA, "(SELECT FullName FROM tblAddressBook WHERE tblCorp.ID = tblAddressBook.ID) AS EntityName, (SELECT FullName FROM tblAddressBook WHERE tblCorp.ResponsibleAttorney = tblAddressBook.ID) AS RespAtty FROM (tblCorp LEFT JOIN tblOrganization ON tblCorp.ID = tblOrganization.ForeignKey) LEFT JOIN tblOfficers ON (tblCorp.ID = tblOfficers.ForeignKey) WHERE (tblCorp.PrimaryContact = ? OR tblCorp.SecondaryContact = ? OR tblOfficers.Name = ?) AND tblOrganization.OrganizedAuthorized = 'O' UNION SELECT DISTINCT tblCorp.ID, tblOrganization.OrganizedAuthorized, tblOrganization.StateOorA, (SELECT FullName FROM tblAddressBook WHERE tblCorp.ID = tblAddressBook.ID) AS EntityName, (SELECT FullName FROM tblAddressBook WHERE tblCorp.ResponsibleAttorney = tblAddressBook.ID) AS RespAtty FROM ((tblCorp LEFT JOIN tblOrganization ON tblCorp.ID = tblOrganization.ForeignKey) LEFT JOIN tblCapital ON tblCorp.ID = tblCapital.ForeignKey) LEFT JOIN tblCertificates ON tblCapital.ID = tblCertificates.ForeignKey WHERE tblCertificates.SecurityHolder = ? AND tblCertificates.Outstanding = 'Yes') AND
tblOrganization.OrganizedAuthorized = 'O'

strSQL2 = SELECT DISTINCT tblCorp.ID, tblOrganization.StateOorA, tblOrganization.OrganizedAuthorized, (SELECT FullName FROM tblAddressBook WHERE tblCorp.ID = tblAddressBook.ID) AS EntityName, (SELECT FullName FROM tblAddressBook WHERE tblCorp.ResponsibleAttorney = tblAddressBook.ID) AS RespAtty FROM tblCorp, tblOrganization
WHERE tblCorp.ResponsibleAttorney = ?? AND tblOrganization.ForeignKey = tblCorp.ID AND
tblOrganization.OrganizedAuthorized = 'O'

strSQL3 = SELECT DISTINCT tblCorp.ID, tblOrganization.OrganizedAuthorized, tblOrganization.StateOorA, (SELECT FullName FROM tblAddressBook WHERE tblCorp.ID = tblAddressBook.ID) AS EntityName, (SELECT FullName FROM tblAddressBook WHERE tblCorp.ResponsibleAttorney = tblAddressBook.ID) AS RespAtty FROM tblCorp RIGHT JOIN tblOrganization ON (tblCorp.ID = tblOrganization.ForeignKey) WHERE tblOrganization.StateOorA = ???

strSQL4 = SELECT DISTINCT tblCorp.ID, tblOrganization.StateOorA, tblOrganization.OrganizedAuthorized, (SELECT FullName FROM tblAddressBook WHERE tblCorp.ID = tblAddressBook.ID) AS EntityName, (SELECT FullName FROM tblAddressBook WHERE tblCorp.ResponsibleAttorney = tblAddressBook.ID) AS RespAtty FROM tblCorp, tblOrganization
WHERE tblCorp.EntityType = ???? AND tblOrganization.OrganizedAuthorized = 'O' AND tblOrganization.ForeignKey = tblCorp.ID

strSQL5 = SELECT DISTINCT tblCorp.ID, tblOrganization.StateOorA, tblOrganization.OrganizedAuthorized, (SELECT FullName FROM tblAddressBook WHERE tblCorp.ID = tblAddressBook.ID) AS EntityName, (SELECT FullName FROM tblAddressBook WHERE tblCorp.ResponsibleAttorney = tblAddressBook.ID) AS RespAtty FROM tblCorp, tblAccounting, tblOrganization WHERE tblAccounting.AccountingFirm = ????? AND tblAccounting.ForeignKey = tblCorp.ID AND tblOrganization.ForeignKey = tblCorp.ID AND tblOrganization.OrganizedAuthorized = 'O'

 

[RiverGuy]

Oh, I see. They pick criteria from different tables? But if they enter no criteria, is data from other tables selected still?

The easiest way without having to build strings as huge as that to me, would be to make a view out of your query, with NO criteria.

Then, let the user query that view with their criteria. It would be like querying one table. Would cut down on your string length.

 

[mdsoren]

I appreciate your assistance and I don't want to sound stupid, but... what's a view? The users only access the database thru the VB app and do not have MS Access installed on their systems. Does any of that matter?

The same fields are pulled no matter what the criteria: company name (=Entity), state the company is in (=StateOorA), whether the company is domiciled in the state or just authorized (=OrganizedAuthorized) and lastly, the attorney responsible for the entity (=RespAtty).

In any event, I will look into using a "view". I am going to be out of town ofr a couple days and will review this thread next Monday should there be any further input.

Thanx River Guy!

 

[RiverGuy]

Access doesn't call view queries anything but queries. A view would contain the following

"Select Table1.One as FROM Table1
UNION Select Table2.One From Table2
UNION SELECT Table3.One From Table2
etc.
etc."

This would be a saved query in Access. You might name it "MyView".

Now you can say "Select * From MyView". Your actual string length in that case would be cut down by 75%+ if you're using 4 select statements in your view.