 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / January 2007How to list records in correspondence table Where [OutDate] Is Null AND [OutType]='06' ?
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 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. [quoted text clipped - 25 lines] > >Applys to: PArray, Button3, frmMainMenu, Do-Item 1195 Kind-a-sort-a but not quite. Thx, Tom. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx >If you want to find the record with the most recent OutDate, you'll >need a Totals query like this: [quoted text clipped - 35 lines] >> >>Applys to: PArray, Button3, frmMainMenu, Do-Item 1195 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 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. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx >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 [quoted text clipped - 13 lines] > >Bruce > 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: [quoted text clipped - 12 lines] > 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 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")); 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.