How to list records in correspondence table Where [OutDate] Is Null AND [OutType]='06' ?

[MLH]

But now here's the catch - I want to see information from an
earlier record in the table.

Suppose the correspondence table had these five records

ID VehicleJobID OutDate OutType
1 150 11/24/06 14
2 151 10/12/06 14
3 152 11/24/06 11
4 150 06
5 151 06
6 152 12/20/06 06

I would like a query to extract & list records 4 and 5 this way:
ID VehicleJobID OutDate OutType Out14Date
4 150 06 11/24/06
5 151 06 10/12/06

where Out14Date is the [OutDate] field of an earlier OutType-14
record in correspondence table for the same vehicles. It's difficult
because the dynaset lists 2 rows containing information from 4
of the table's records. Extracting records 4 & 5 is a cake walk.
I mean you're only looking for OutType-06 records with Null OutDates.
But, as the query is finding these records, having it make note of
the VehicleJobID's that turn up, then rush backwards through the
same table to find an earlier OutType-14 record for the same vehicle
and including that data in the dynaset ==now that's a chore.
Applys to: PArray, Button3, frmMainMenu, Do-Item 1195

[Tom van Stiphout]

On Wed, 20 Dec 2006 09:09:17 -0500, MLH <CR**@NorthState.netwrote:

If you want to find the record with the most recent OutDate, you'll
need a Totals query like this:
select VehicleJobID, Max(OutDate)
from Correspondence
group by VehicleJobID
Save this query. Then create a second query with the table and the
first query, joining on VehicleJobID.

-Tom.

>But now here's the catch - I want to see information from an
earlier record in the table.

Suppose the correspondence table had these five records

ID VehicleJobID OutDate OutType
1 150 11/24/06 14
2 151 10/12/06 14
3 152 11/24/06 11
4 150 06
5 151 06
6 152 12/20/06 06

I would like a query to extract & list records 4 and 5 this way:
ID VehicleJobID OutDate OutType Out14Date
4 150 06 11/24/06
5 151 06 10/12/06

where Out14Date is the [OutDate] field of an earlier OutType-14
record in correspondence table for the same vehicles. It's difficult
because the dynaset lists 2 rows containing information from 4
of the table's records. Extracting records 4 & 5 is a cake walk.
I mean you're only looking for OutType-06 records with Null OutDates.
But, as the query is finding these records, having it make note of
the VehicleJobID's that turn up, then rush backwards through the
same table to find an earlier OutType-14 record for the same vehicle
and including that data in the dynaset ==now that's a chore.
Applys to: PArray, Button3, frmMainMenu, Do-Item 1195

[MLH]

Kind-a-sort-a but not quite. Thx, Tom.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Wed, 20 Dec 2006 07:32:42 -0700, Tom van Stiphout
<no*************@cox.netwrote:

>On Wed, 20 Dec 2006 09:09:17 -0500, MLH <CR**@NorthState.netwrote:

If you want to find the record with the most recent OutDate, you'll
need a Totals query like this:
select VehicleJobID, Max(OutDate)
from Correspondence
group by VehicleJobID
Save this query. Then create a second query with the table and the
first query, joining on VehicleJobID.

-Tom.

>>But now here's the catch - I want to see information from an
earlier record in the table.

Suppose the correspondence table had these five records

ID VehicleJobID OutDate OutType
1 150 11/24/06 14
2 151 10/12/06 14
3 152 11/24/06 11
4 150 06
5 151 06
6 152 12/20/06 06

I would like a query to extract & list records 4 and 5 this way:
ID VehicleJobID OutDate OutType Out14Date
4 150 06 11/24/06
5 151 06 10/12/06

where Out14Date is the [OutDate] field of an earlier OutType-14
record in correspondence table for the same vehicles. It's difficult
because the dynaset lists 2 rows containing information from 4
of the table's records. Extracting records 4 & 5 is a cake walk.
I mean you're only looking for OutType-06 records with Null OutDates.
But, as the query is finding these records, having it make note of
the VehicleJobID's that turn up, then rush backwards through the
same table to find an earlier OutType-14 record for the same vehicle
and including that data in the dynaset ==now that's a chore.
Applys to: PArray, Button3, frmMainMenu, Do-Item 1195

[deluxeinformation]

What I'm guessing you want to do is list all correspondence records
with a Null OutDate and the OutDate of a previous correspondence record
with the same vehiclejobID and latest OutDate. If so, perhaps this
will work:

SELECT Correspondence.ID, Correspondence.VehicleJobID,
Correspondence.OutDate, Correspondence.OutType,
PreviousCorrespondence.OutDate14
FROM Correspondence INNER JOIN [SELECT Correspondence.VehicleJobID,
Max(Correspondence.OutDate) AS OutDate14
FROM Correspondence
WHERE Correspondence.OutType=14
GROUP BY Correspondence.VehicleJobID]. AS PreviousCorrespondence ON
Correspondence.VehicleJobID = PreviousCorrespondence.VehicleJobID
WHERE Correspondence.OutDate Is Null;

Bruce

[MLH]

Bruce, I think you may be on to something here. But I don't have two
different tables. I have tblCorrespondence only. You have named two:
Correspondence and PreviousCorrespondence. Perhaps there is a meaning
to your example that I can interpret and use, but I am having some
trouble doing so. I appreciate you taking a stab at it.

Simply stated, what is expected out of the final query is this: It is
to list certain records from tblCorrespondence meeting specified
criteria in the [OutDate] and [OutType] fields ==but only those
records for which earlier records of the same [VehicleJobID] field
value with [OutDate] and [OutType] fields of specified criteria exist
in the SAME table. Well, maybe not simply stated, but accurately
stated.

Forgive me if I have completely misinterpreted the meaning you were
trying to convey with the example.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxx

On 20 Dec 2006 11:50:47 -0800, de***************@gmail.com wrote:

>What I'm guessing you want to do is list all correspondence records
with a Null OutDate and the OutDate of a previous correspondence record
with the same vehiclejobID and latest OutDate. If so, perhaps this
will work:

SELECT Correspondence.ID, Correspondence.VehicleJobID,
Correspondence.OutDate, Correspondence.OutType,
PreviousCorrespondence.OutDate14
FROM Correspondence INNER JOIN [SELECT Correspondence.VehicleJobID,
Max(Correspondence.OutDate) AS OutDate14
FROM Correspondence
WHERE Correspondence.OutType=14
GROUP BY Correspondence.VehicleJobID]. AS PreviousCorrespondence ON
Correspondence.VehicleJobID = PreviousCorrespondence.VehicleJobID
WHERE Correspondence.OutDate Is Null;

Bruce

[MLH]

A working solution is to have 2 querys do the job. Below, Query6 grabs
all correspondence records of specified OutDate and OutType. Query7
grabs all correspondence records of specified (but different) OutDate
and OutType #AND# Query7 includes Query6 in the QBE grid to further
limit Query7's output. Now I know a single saved query can be
constructed to achieve achieve the same results I'm now using these
2 queries for. But I don't know how to build it. I recall seeing such
examples in this NG over the years.

This is saved as Query6:
SELECT tblCorrespondence.CorrespID, tblCorrespondence.VehicleJobID,
tblCorrespondence.OutDate, tblCorrespondence.OutType
FROM tblCorrespondence
WHERE (((tblCorrespondence.OutDate) Is Not Null) AND
((tblCorrespondence.OutType)="14"));

This is saved as Query7
SELECT tblCorrespondence.CorrespID, tblCorrespondence.VehicleJobID,
tblCorrespondence.OutDate, tblCorrespondence.OutType, Query6.OutDate
AS Type14OutDate
FROM tblCorrespondenceINNER JOIN Query6 ON
tblCorrespondence.VehicleJobID = Query6.VehicleJobID
WHERE (((tblCorrespondence.OutDate) Is Null) AND
((tblCorrespondence.OutType)="06"));

[MLH]

Wait! Wait! Wait! Here it is! I got it!

SELECT VehicleJobID, OutType, EXISTS (SELECT VehicleJobID FROM
tblCorrespondence AS tblC
WHERE tblC.VehicleJobID = tblCorrespondence.VehicleJobID AND
tblC.OutType = "14" AND tblC.OutDate Is Not Null) AS
YepNope FROM tblCorrespondence WHERE tblCorrespondence.OutType = "06"
AND tblCorrespondence.OutDate Is Null

I'm not much of an SQL person. If the query wizard builders cannot
build it, most of the time I just have to do without. As cool as the
wizards are, they can't do stuff like the above little EXISTS
thing-a-ma-bobber.

This little tidbit was contrbuted 99.99% by Leigh Purvis, a genius by
from the UK. Thanks again Leigh.

[deluxeinformation]

MLH wrote:

Bruce, I think you may be on to something here. But I don't have two
different tables. I have tblCorrespondence only. You have named two:
Correspondence and PreviousCorrespondence. Perhaps there is a meaning
to your example that I can interpret and use, but I am having some
trouble doing so. I appreciate you taking a stab at it.

Simply stated, what is expected out of the final query is this: It is
to list certain records from tblCorrespondence meeting specified
criteria in the [OutDate] and [OutType] fields ==but only those
records for which earlier records of the same [VehicleJobID] field
value with [OutDate] and [OutType] fields of specified criteria exist
in the SAME table. Well, maybe not simply stated, but accurately
stated.

Forgive me if I have completely misinterpreted the meaning you were
trying to convey with the example.

No apologies necessary. What you are seeing as a second table
(PreviousCorrespondence) is simply an alias for the first table
(Correspondence). Take the SQL I've given you and drop it into the
query designer in SQL view, then switch to design view, and you'll get
a better idea of what it does. As you've stated in later posts, you
can easily do this with two queries. This is simply a method of doing
it with a single query.

Bruce