Compare lowest and second lowest records

[Guest]

I have two tables:
TABLE1:
Contract Request Date
123 April 1, 2005, 8am
123 April 2, 2005, 9am
123 April 8, 2005, 9am
TABLE2:
Contract Reply Date
123 April 2, 2005, 10am
123 April 8, 2005, 10 am

I would need a query that will return:
Contract RequestDate ReplyDate
123 April1, 2005 8am No Reply
123 April 2, 2005, 9am April 2, 2005, 10am
123 April 8, 2005, 9am April 8, 2005, 10am

What I have so far is:
select Contract, RequestDate, min(ReplyDate) from table1, table2 where
table1.contract=table2.contract and Request Date<Reply Date

I just can't get the "No Reply" record to display. i would need some kind of
code to display "No Reply" if Request Date(n)<min(ReplyDate) and Request
Date(n+1)<min(ReplyDate).

 

[Guest]

You need an outer join query, which displays all values of one table
regardless of whether they are matched in the other table.

To do this, double-click the line joining the two tables in your query grid.
This will bring up the Join Properties dialog box. Select the radio button
that will include all records of TABLE 1, and OK to close the form. The
query should then give you what you want