[AccessD] Unmatched query problem

Kaup, Chester Chester_Kaup at kindermorgan.com
Wed Sep 21 14:16:10 CDT 2005


That worked great. Thanks once again to great list members.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Wednesday, September 21, 2005 1:36 PM
To: accessd at databaseadvisors.com
Subject: Re: [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


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com






More information about the AccessD mailing list