SQL - Finding the earliest date in table1, later than date in table2

[Marco123]

Hi there,

I am trying to create an SQL query to find the record that corresponds to the earliest date in one table, that is after a certain date in another table.

I have two tables, tblMain and tblApplicants.

In tblDates, there are 2 columns with 20 records. In tblApplicants, there are 3 columns, but over 100 records.

tblMain looks like this:

DateID, Date
1 01/01/2010
2 10/01/2010
3 25/01/2010
4 02/02/2010
etc

tblApplicants looks like this:

FirstName, LastName, DateofApplication
Joe Blogs 16/01/2010
John Smith 04/02/2010
etc

Basically, for every record in tblApplicants, I am trying to find the earliest date (and corresponding DateID) in tblMain, where the Date is later than the DateofApplication in tblApplicants.

For example, if the DateofApplication is 16/01/2010, the earliest date after this in tblMain would be 25/01/2010 and the corresponding DateID would be 3.

I have attempted to create an SQL query for this as shown below, but I’m not entirely sure how to construct it and, moreover, how to get it work for every single record in tblApplicants.

SELECT tblMain.DateID, tblMain.[Date]
FROM tblApplicants
WHERE tblMain.[Date] = ((SELECT MIN(tblMain.[Date])
FROM tblMain
WHERE tblMain.[Date] > (SELECT DateofApplication FROM tblApplicants))

Any help would be greatly appreciated.

Thanks,

Marco

 

[PHV]

What about this ?
SELECT A.FirstName, A.LastName, A.DateofApplication, M.DateID, M.Date
FROM tblApplicants A, tblMain M
WHERE M.Date = (SELECT Min([Date]) FROM tblMain WHERE [Date]>A.DateofApplication)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Marco123]

Brilliant, thanks that works like a dream. Never thought it would be so simple in the end.

I had no idea how I would join the tables as I only needed to use one table (tblMain) as a reference for the criteria that would be applied to all records in the other table (tblApplicants). Wonder if there is a way to do this using joins.....

 

[PHV]

My suggestion use a CROSS JOIN (aka cartesian product).

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886