return based on whether there's another corresponding record?

[dpav29]

Here's what I'm racking my brain over.

The table:

Rec# ID Date1 Date2 etc. etc.
1 10 1/1
2 10 1/1 3/1
3 20 1/1
4 30 1/1
5 30 1/1 3/15

I hope this makes sense! I want the query to return records with null values in Date2 UNLESS is a higher record# for the same ID and Date1. . .then I want the higher returned. So, using my lame table above, I would want the query to return records 2, 3 and 5.

Can anyone help?

 

[Golom]

Try
[blue][tt]
Select ID,

(IIF ( (Select MAX(Date2) From tbl X
Where X.ID = T.ID AND X.[Rec#] > T.[Rec#]) IS NULL,
NULL,
(Select MAX(Date2) From tbl X
Where X.ID = T.ID AND X.[Rec#] > T.[Rec#]) )) As [Date 2]

From tbl T

Group By T.ID
[/tt][/blue]

 

[dpav29]

I appreciate the help, but I think this is much more than I need. . . I didn't do a good enough job in my example. Let me explain. The two dates are in the same table, "AuditHistory". Fields are actually called "ScheduledDate" and "Next" and the date range is selected by another form. Here's what the current query looks like:

SELECT AuditHistory.Next, AuditHistory.ScheduledDate, Organization.OrgID, Organization.OrgName, AuditHistory.Date, AuditTools.AuditTool, ResultsExecSum.Result, AuditHistory.ModSchedDate, AuditHistory.ActionPlan
FROM Organization INNER JOIN ((AuditHistory LEFT JOIN AuditTools ON AuditHistory.AuditToolNo = AuditTools.ToolID) LEFT JOIN ResultsExecSum ON AuditHistory.Result = ResultsExecSum.ResultID) ON Organization.OrgID = AuditHistory.OrgNum
WHERE (((AuditHistory.ScheduledDate) Between [Forms]![frmSelectReport]![txtFrom] And [Forms]![frmSelectReport]![txtTo]))
ORDER BY AuditHistory.Next DESC , Organization.OrgName;


I primarily use the QBD grid, so you're code is a little confusing. However, I understand it enough to see what you're getting at. What I'm thinking is using some form of what you suggest in an "IIF" expression in the "scheduledDate" filed . . .ie IIF MAX(Next) From AuditHistory (this is where I get lost)
Where AudithistoryID = T.ID AND X.[Rec#] > T.[Rec#]) IS NULL,
NULL,
(Select MAX(Date2) From tbl X
Where X.ID = T.ID AND X.[Rec#] > T.[Rec#]) )) As [ScheduledDate]

I tried many differed ways, but just became more frustrated. If you'd be willing to help some more, I'd be very grateful!

Dave