How can I adjust this SQL query?

ajtruckle · October 6th, 2007, 01:54 PM

OK, I have what I want! At the moment I have three seperate queries.

Andrew:

Code:

SELECT [Home Talks].[Congregation], Max([Home Talks].[Last Given]) AS MostRecentDate
FROM [Home Talks]
GROUP BY [Home Talks].[Congregation]
UNION SELECT [Congregations], [Last Invited]
FROM [Congregations]
ORDER BY [MostRecentDate];

Andrew1:

Code:

SELECT Andrew.Congregation, Max(Andrew.MostRecentDate) AS MaxOfMostRecentDate
FROM Andrew
GROUP BY Andrew.Congregation;

Andrew2:

Code:

SELECT Andrew1.Congregation, Congregations.Time, Andrew1.MaxOfMostRecentDate, IIf([MaxOfMostRecentDate]>Now(),"Booked","Old Date") AS Status
FROM Andrew1 INNER JOIN Congregations ON Andrew1.Congregation = Congregations.Congregations
ORDER BY MaxOfMostRecentDate DESC;

This gives me the table I want. I have all the details I want and the date is the right date and the sorting works because the date field is still a date field (the UNION came to the rescue). The IIF or Switch seems to return strings.

So, this does what I want. The question is, can I combine these into one SQL statement?