Query to extract only specific records....

[jeep2001]

Hi...
I have data in a log table that resembles the following list. I am working on a query that I need to pull out all records that were restored during the year. If a record was closed it would have a close date and a "Y" in the indicator field, and if it was restored, it would be followed with a record containing a date (restored date value) in the closed date and a blank in the indicator field.
In summary, I only want the record that closed, and the following record that shows it was restored.


ACCT ACCTNAME CLOSE DATE CLOSE INDICATOR
F65 GRAHAM WILLIAM W 9/6/2005 Y
F65 GRAHAM WILLIAM W 12/8/2005
F7E JAMES B
F7J MITCHELL 1/31/2005 Y
F7J MITCHELL 5/18/2005

 

[lynchg]

Try this:

Select A1.Acct, A1.AcctName, A1.CloseDate, A1.CloseIndicator
From Account A1, Account A2
Where A1.Acct = A2.Acct
And A1.CloseIndicator = 'Y'
Order By A1.Acct, A1.AcctName
Union
Select A2.Acct, A2.AcctName, A2.CloseDate, A2.CloseIndicator
From Account A1, Account A2
Where A1.Acct = A2.Acct
And A1.CloseIndicator = 'Y'
Order By A2.Acct, A2.AcctName

 

[PHV]

Have a look here:

http://www.sco.com/support/docs/unixware/

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, sorry, wrong thread :~/

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jeep2001]

Hi

Thanks for the responses. I tried the SQL it seems close.
The result set should look like this. The union is not getting the ' ' records, only the ones with the indicator = 'Y'.
Now one thing...I cannot use the Access desgin grid as this is from a DB2 back end so I am using a pass thru.

F65 GRAHAM WILLIAM W 9/6/2005 Y
F65 GRAHAM WILLIAM W 12/8/2005
F7J MITCHELL 1/31/2005 Y
F7J MITCHELL 5/18/2005

 

[jeep2001]

Hi

I was able to solve the query..but I went about it differently. I built a pass thru query to return all the 'Y' records (closed accts) then another to get all the reinstated (' ')...then i used the access query grid to link and fine tune, and it worked perfectly.