Only show most recent invoice?

[Jim]

I need to create a query that only shows the most recent invoice for a
customer and what was ordered for a history report. How could I do that
using the Northwind database as an example? I'm using Access 2003.
Thanks

Jim

 

[Smartin]

I need to create a query that only shows the most recent invoice for a
customer and what was ordered for a history report. How could I do that
using the Northwind database as an example? I'm using Access 2003.
Thanks

Jim

Hi Jim,

First create a totals query based on Orders. Group By Customer ID and
get Max(Order Date). Save this query as "RecentCO"

Next create a query based on tables Customers, Orders, and Order
Details, plus query RecentCO. The three tables should create appropriate
joins automatically. Make sure RecentCO joins to Customers.CustomerID
and Orders.OrderDate.

Pluck out all the fields you want to see. Save this query as
RecentCODetails.

That's it!

When I tested this the resulting queries came out like this

Query RecentCO:
----------------
SELECT Orders.CustomerID, Max(Orders.OrderDate) AS MaxOfOrderDate
FROM Orders
GROUP BY Orders.CustomerID;

Query RecentCODetails:
-----------------------
SELECT Customers.CompanyName, [Order Details].*
FROM ((Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) INNER JOIN RecentCO ON (RecentCO.MaxOfOrderDate =
Orders.OrderDate) AND (Customers.CustomerID = RecentCO.CustomerID))
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID;


Have a great weekend,

 

[Jim]

Thanks! That worked great.

I need to create a query that only shows the most recent invoice for a
customer and what was ordered for a history report. How could I do that
using the Northwind database as an example? I'm using Access 2003.
Thanks

Jim

Hi Jim,

First create a totals query based on Orders. Group By Customer ID and get
Max(Order Date). Save this query as "RecentCO"

Next create a query based on tables Customers, Orders, and Order Details,
plus query RecentCO. The three tables should create appropriate joins
automatically. Make sure RecentCO joins to Customers.CustomerID and
Orders.OrderDate.

Pluck out all the fields you want to see. Save this query as
RecentCODetails.

That's it!

When I tested this the resulting queries came out like this

Query RecentCO:
----------------
SELECT Orders.CustomerID, Max(Orders.OrderDate) AS MaxOfOrderDate
FROM Orders
GROUP BY Orders.CustomerID;

Query RecentCODetails:
-----------------------
SELECT Customers.CompanyName, [Order Details].*
FROM ((Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) INNER JOIN RecentCO ON (RecentCO.MaxOfOrderDate =
Orders.OrderDate) AND (Customers.CustomerID = RecentCO.CustomerID)) INNER
JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID;


Have a great weekend,