[AccessD] Unmatched query problem

Hi Chester

First create a query using a Cartesian (multiplying) join to list all possible wells and dates (which means where at least one well was active):

  SELECT 
    A.WellNo,
    B.TestDate
  FROM 
    tblWellDates AS A, 
    tblWellDates AS B
  GROUP BY 
    A.WellNo,
    B.TestDate;

Save this as, say, qryAll.
Then use this to find wells/dates missing:

  SELECT 
    qryAll.WellNo, 
    qryAll.TestDate
  FROM 
    qryAll
  LEFT JOIN 
    tblWellDates ON
      (qryAll.WellNo = tblWellDates.WellNo)
        AND 
      (qryAll.TestDate = tblWellDates.TestDate) 
  WHERE 
    tblWellDates.WellNo Is Null;

/gustav


> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
> Sent: September 21, 2005 12:24 PM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Unmatched query problem
>
> I have one master table of well numbers. In another table I have well
> numbers of wells that are active by date. I need to gat a list of wells
> that are not active on each date. The unmatched query wizard does not
> care about the date. How might I do this? Thanks