Form input PLUS multiple criteria from same field?

[DerekMcDonald]

Hi! This would seem to be a pretty common query, but I can't find many helpful answers on how to set it up. Please bear with me and read on:

I've got a contacts database. Table 1 holds the contacts names and addresses. Table 3 holds the contact types (Board Member, Donor, Ex-Staff, etc.), and types can be added and deleted through a form. Table 2 links tables 1 and three together via the primary key. The user can thus choose multiple types for each contact on a form with a combo box.

Now, I'm trying to base a query on a form where the user can select all contacts where type = a AND b AND c. So the user can print a list of contacts who are Donors AND Ex-Staff.

I've set up a form where the user can run a query to find all contacts based on the values in up to three combo boxes. The values of the combo boxes are sent to a query based on the link table, returning all records where the TYPE's primary key equals the keys in the form's combo boxes. Then a subquery is run on the first query to find the duplicate values of the contact's primary key. This query is set to return the top value, in theory weeding out the contacts who return only two duplicates when three combo boxes on the form were filled.

And this works, except for the following situation. If three combo boxes are filled on the form (Find all CONTACTS Where TYPE is like A, B and C) but no contacts in the database match the criteria, a list of contacts is returned where the contacts match only two of the criteria, and the user has no way of knowing that the returns don't meet all the criteria.

Thanks for reading. I've been trying to figure this out for months- it's my holy grail. Any help would be greatly appreciated!

 

[Psychpt]

what is the sql from the query?
if you post a copy of it we might be able to help you.

PsychPt@Hotmail.com

 

[DerekMcDonald]

Here's the SQL from the first query:

SELECT tblIdenCategories.ID, tblIdenCategories.ID_IdenCat
FROM tblIdenCategories
WHERE (((tblIdenCategories.ID_IdenCat) Like [forms]![frm_GetReportCategories]![cmbCats1] Or (tblIdenCategories.ID_IdenCat) Like [forms]![frm_GetReportCategories]![cmbCats2] Or (tblIdenCategories.ID_IdenCat) Like [forms]![frm_GetReportCategories]![cmbCats3]));

The second query pulls the duplicates from this one and selects the top value of the number of duplicates:

SELECT DISTINCTROW TOP 1 Count(qry_qbf_IdenCatsOr.ID) AS NumberOfDups, First(qry_qbf_IdenCatsOr.ID) AS [ID Field], qry_qbf_IdenCatsOr.ID
FROM qry_qbf_IdenCatsOr
GROUP BY qry_qbf_IdenCatsOr.ID
HAVING (((Count(qry_qbf_IdenCatsOr.ID))>1))
ORDER BY Count(qry_qbf_IdenCatsOr.ID) DESC;

A third query inner-joins the contacts list with the results:

SELECT Main.ID, Main.ORG, Main.PREFIX, Main.MIDINIT, Main.FNAME, Main.LNAME, Main.TITLE, Main.STREET, Main.STREET2, Main.CITY, Main.STATE, Main.ZIP
FROM Main INNER JOIN qry_qbf_IdenCatsAnd ON Main.ID = qry_qbf_IdenCatsAnd.ID
GROUP BY Main.ID, Main.ORG, Main.PREFIX, Main.MIDINIT, Main.FNAME, Main.LNAME, Main.TITLE, Main.STREET, Main.STREET2, Main.CITY, Main.STATE, Main.ZIP
ORDER BY Main.ORG, Main.LNAME;

Quite a soup, eh? Perhaps there's something a lot simpler that I'm overlooking...