This is a cool question, and you can do this with set logic.
*I assume business rule that no two inspections for a company will occur on the same date--if this is not the case you'll have to incorporate time comparison as well as date.
I did the following with Northwind Customers<Orders
1. Get latest Order for Customer:
[tt]SELECT Customers.CompanyName, Max(Orders.OrderDate) AS last_order
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CompanyName;
[/tt]
2. Get the Order for the Customer previous to the last:
[tt]SELECT Customers.CompanyName,
Max(Orders.OrderDate) AS next_to_last_order
FROM (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN last_order_each_customer ON (Customers.CompanyName = last_order_each_customer.CompanyName) AND (Orders.OrderDate < last_order_each_customer.last_order)
GROUP BY Customers.CompanyName;[/tt]
3. Join the results sorted by Customer, Order with a UNION ((an alias of "last two orders" would be better for second column):
[tt]SELECT
last_order_each_customer.CompanyName,
last_order_each_customer.last_order
FROM last_order_each_customer;
UNION
SELECT
next_to_last_order_customer.CompanyName, next_to_last_order_customer.next_to_last_order
FROM next_to_last_order_customer
ORDER BY 1,2 DESC[/tt]
Results:
[tt]CompanyName last_order
Alfreds Futterkiste 4/9/1998
Alfreds Futterkiste 3/16/1998
Ana Trujillo Emparedados y helados 3/4/1998
Ana Trujillo Emparedados y helados 11/28/1997
Antonio Moreno Taquería 1/28/1998
Antonio Moreno Taquería 9/25/1997
Around the Horn 4/10/1998
Around the Horn 3/16/1998
Berglunds snabbköp 3/4/1998
Berglunds snabbköp 2/6/1998[/tt]
Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development