Traveled in one date range but not another

  • Thread starter Thread starter nick
  • Start date Start date
N

nick

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
 
Here's one way:

SELECT CUSTFILE.*
FROM CUSTFILE
WHERE CUSTFILE.CustomerNo IN
(SELECT CustomerNo FROM CUSTHIST
WHERE Year(TourStart) = 1997)
AND
CUSTFILE.CustomerNo IN
(SELECT CustomerNo FROM CUSTHIST
WHERE Year(TourStart) = 1998)
AND
CUSTFILE.CustomerNo IN
(SELECT CustomerNo FROM CUSTHIST
WHERE Year(TourStart) = 2003)
AND
CUSTFILE.CustomerNo NOT IN
(SELECT CustomerNo FROM CUSTHIST
WHERE Year(TourStart) BETWEEN 1999 And 2002)

You could alternatively create and save four queries - one for each of the
four subqueries. Use INNER JOIN on the first three and OUTER JOIN with an
IS NULL test on the last one.
--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
Thanks for the help. What does the SELECT CUSTFILE.* do for the query?
Does that pull everything from that table into the query? I copied
this into Access and ran it but it locked up.

I am going to try the four seperate queries.
 
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
 
Yes, CUSTFILE.* asks for all columns from the table. Which version of
Access are you using? The query should run just fine. You could also try:

SELECT CUSTFILE.*
FROM (((CUSTFILE INNER JOIN
(SELECT CustomerNo FROM CUSTHIST
WHERE Year(TourStart) = 1997) As C97
ON CUSTFILE.CustomerNo = C97.CustomerNo)
INNER JOIN
(SELECT CustomerNo FROM CUSTHIST
WHERE Year(TourStart) = 1998) As C98
ON CUSTFILE.CustomerNo = C98.CustomerNo)
INNER JOIN
(SELECT CustomerNo FROM CUSTHIST
WHERE Year(TourStart) = 2003) As C03
ON CUSTFILE.CustomerNo = C03.CustomerNo)
LEFT JOIN
(SELECT CustomerNo FROM CUSTHIST
WHERE Year(TourStart) BETWEEN 1999 And 2002) As CNot
ON CUSTFILE.CustomerNo = CNot.CustomerNo
WHERE CNot.CustomerNo IS NULL;

If you have tens of thousands of records in CUSTHIST, this could run very
slowly because Access cannot optimize the Year(TourStart) tests. It might
run much faster if you change these to something like:

WHERE TourStart BETWEEN #1/1/1997# AND #12/31/1997#

... especially if you have an index on the TourStart field.
--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
Back
Top