I've searched the forums but can't find exactly what I need. In Access 2000 I have an Establishments table and an Inspections table. I need to find the two most recent inspections for each establishment. I've tried the "Top 2" option but it returns only the top two records from the entire table, not the top two for each establishment. I've also tried a query within a query without much luck. Any help is appreciated!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Pulling two most recent records 2
- Thread starter beverlee
- Start date
-
1
- #2
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
*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
- Thread starter
- #4
-
1
- #5
the "top 2" option will work if you use it in a correlated subquery
[tt]select Customers.CompanyName
, This.OrderDate
from Customers
inner
join Orders This
on Customers.CustomerID = This.CustomerID
where This.OrderDate
in ( select top 2
OrderDate
from Orders
where CustomerID = This.CustomerID )[/tt]
rudy
SQL Consulting
[tt]select Customers.CompanyName
, This.OrderDate
from Customers
inner
join Orders This
on Customers.CustomerID = This.CustomerID
where This.OrderDate
in ( select top 2
OrderDate
from Orders
where CustomerID = This.CustomerID )[/tt]
rudy
SQL Consulting
Cleaner still--I likes it!
Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development
Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development
Rudy:
Thanks for putting that up! I didn't know that the nested, correlated query could look for criteria that were present in the outer query.
Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development
Thanks for putting that up! I didn't know that the nested, correlated query could look for criteria that were present in the outer query.
Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development
However when using your SQL against the same Northwind copy I get this result set:
[tt]
Company Name Order Date
Alfreds Futterkiste 25-Aug-1997
Alfreds Futterkiste 03-Oct-1997
Ana Trujillo Emparedados y helados 18-Sep-1996
Ana Trujillo Emparedados y helados 08-Aug-1997
Antonio Moreno Taquería 27-Nov-1996
Antonio Moreno Taquería 15-Apr-1997
[/tt]
Using the submission I put in above I get:
[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
[/tt]
Not sure yet what accounts for the discrepancy...
Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development
[tt]
Company Name Order Date
Alfreds Futterkiste 25-Aug-1997
Alfreds Futterkiste 03-Oct-1997
Ana Trujillo Emparedados y helados 18-Sep-1996
Ana Trujillo Emparedados y helados 08-Aug-1997
Antonio Moreno Taquería 27-Nov-1996
Antonio Moreno Taquería 15-Apr-1997
[/tt]
Using the submission I put in above I get:
[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
[/tt]
Not sure yet what accounts for the discrepancy...
Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development
It would appear that JET SQL does support this...here's a section from Help ("subqueries"
:
[tt]
You can also use table name aliases in a subquery to refer to tables listed in a FROM clause outside the subquery. The following example returns the names of employees whose salaries are equal to or greater than the average salary of all employees having the same job title. The Employees table is given the alias "T1":
SELECT LastName,
FirstName, Title, Salary
FROM Employees AS T1
WHERE Salary >=
(SELECT Avg(Salary)
FROM Employees
WHERE T1.Title = Employees.Title) Order by Title;
[/tt]
Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development
:[tt]
You can also use table name aliases in a subquery to refer to tables listed in a FROM clause outside the subquery. The following example returns the names of employees whose salaries are equal to or greater than the average salary of all employees having the same job title. The Employees table is given the alias "T1":
SELECT LastName,
FirstName, Title, Salary
FROM Employees AS T1
WHERE Salary >=
(SELECT Avg(Salary)
FROM Employees
WHERE T1.Title = Employees.Title) Order by Title;
[/tt]
Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development
Correct--I was forgetting that TOP is entirely arbitrary vis a vis the result set (selects "first n" without reference to an order unless provided in results, unlike MAX/MIN--Access Help: If you do not include the ORDER BY clause, the query will return an arbitrary set of 25 records from the Students table that satisfy the WHERE clause.).
Just for reference purposes the order history in its entirety for the first company is: [tt]
Company Name Order Date
Alfreds Futterkiste 09-Apr-1998
Alfreds Futterkiste 16-Mar-1998
Alfreds Futterkiste 15-Jan-1998
Alfreds Futterkiste 13-Oct-1997
Alfreds Futterkiste 03-Oct-1997
Alfreds Futterkiste 25-Aug-1997[/tt]
To get the most recent dates for each company in the subquery AND show them last/next to last for each company in the results it requires ORDER BY in both (I'm not preaching here just sharing my own practice efforts):
[tt]
select
customers.companyname,
this.orderdate
from customers
inner join
orders this
on customers.customerid = this.customerid
where
this.orderdate in
(select top 2 orderdate
from orders
where customerid = this.customerid
order by orderdate desc)
order by 1,2 desc;
[/tt]
Cheers,
Jeff
Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development
Just for reference purposes the order history in its entirety for the first company is: [tt]
Company Name Order Date
Alfreds Futterkiste 09-Apr-1998
Alfreds Futterkiste 16-Mar-1998
Alfreds Futterkiste 15-Jan-1998
Alfreds Futterkiste 13-Oct-1997
Alfreds Futterkiste 03-Oct-1997
Alfreds Futterkiste 25-Aug-1997[/tt]
To get the most recent dates for each company in the subquery AND show them last/next to last for each company in the results it requires ORDER BY in both (I'm not preaching here just sharing my own practice efforts):
[tt]
select
customers.companyname,
this.orderdate
from customers
inner join
orders this
on customers.customerid = this.customerid
where
this.orderdate in
(select top 2 orderdate
from orders
where customerid = this.customerid
order by orderdate desc)
order by 1,2 desc;
[/tt]
Cheers,
Jeff
Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development
- Status
Similar threads
- Locked
- Question
- Locked
- Question