Nick,
If you are going to have to do this type of a query in the future, the
following technique should work. Create a table called tbl_Years with
fields: TourYears (Long) and qryTraveled (Yes/No). This table should
include all the years that you are interested in. If you want to know
that they traveled during the year, chk the qryTraveled box. If you
want to check that they didn't travel in a year, leave the box
unchecked. If you don't care whether they traveled in a year, leave
the year out of the table. You could modify this technique to set
qryTraveled to Yes, No, or Don't care by changing the data type to
integer and modifying the final query slightly.
Now, create a query (qyr_CustomerYears) to give you a cartesian join
of all the customers with all the years.
SELECT tbl_Customer.CustID
, tbl_Years.TourYear
, tbl_Years.qryTraveled
FROM tbl_Customer, tbl_Years;
Next, create a query (qry_History) to give you a recordset of the
years that each customer traveled. The reason I explicitly set the
TravelYear as Clng was to make sure that the field type would join to
the previous query properly.
SELECT DISTINCT [tbl_CustHistory].[CustID]
, Clng(Year([TourStart])) AS TravelYear
FROM tbl_CustHistory;
Lastly, create another query (qry_MeetsCriteria) to join these two
tables, count the number of records that match, compare that with the
number of records in tbl_Years, and then select the appropriate fields
from your customers table that meet your criteria. The key to this is
the subquery. This subquery joins qry_CustomerYears to qry_History
using a left join (include all records from qry_CustomerYears). It
then creates a computed column, denoted by the IIF() statements that
will have a value of 1 or 0. If the qryTraveled field is checked, and
qry_History has a matching record, then the first IIF() will return a
1. The imbedded IIF() will determine if the qryTraveled field is not
checked and there is no matching record (this means they didn't
actually travel in that year). If this is the case, this IIF() will
also return a 1. Lastly, if neither one of those conditions is true,
then the it will return a 0. The GroupBy clause sums this computed
column by customer and the HAVING clause checks to see whether the
SUM() equals the number of records in tbl_Years.
SELECT *
FROM tbl_Customer
WHERE CustID IN
(SELECT C.CustID
FROM qry_CustomerYears AS C
LEFT JOIN qry_History
ON (C.TourYear = qry_History.TravelYear)
AND (C.CustID = qry_History.CustID)
GROUP BY C.CustID
HAVING (((Sum(IIf([qryTraveled] And Not IsNull([TravelYear]),1,IIf(Not
[qryTraveled] And IsNull([TravelYear]),1,0))))=(SELECT COUNT(*) FROM
tbl_Years))));
--
HTH
Dale Fye
I posted this eariler but can't get it to work. I have a database that
has multiple tables. I'm using the Customer File table "CUSTFILE" and
the Customer History table "CUSTHIST". I need travelers who traveled
in 97 and 98 did not travel in 99 - 02 then traveled again in 03. I
have a CustomerNo, Custname, Address1, Address2, City, State, Zip in
the "CUSTFILE" table and CustomerNo, Custname, TourStart and address
info in "CUSTHIST" table. I tried to create multiple queries and
compare them but could not get the correct results. Any help would be
appriciated.
Nick
