show last record from related table in access

[bri]

i have a one to many relationship for a customers and orders table. how do i
get the last entered order for each customer

 

[Vincent Johns]

i have a one to many relationship for a customers and orders table. how do i
get the last entered order for each customer

Unless you have some field in each record in [orders] indicating the
date (and maybe time) of each order, you won't be able to determine
which was last entered.

Assuming you do have that, you can use a Query like the following one
(which targets Tables in the "Northwind Traders" database):

SQL view:

SELECT DISTINCTROW Orders.CustomerID, Orders.OrderDate,
Orders.OrderID, Orders.EmployeeID, Orders.RequiredDate,
Orders.ShippedDate, Orders.ShipVia, Orders.Freight,
Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
Orders.ShipRegion, Orders.ShipPostalCode,
Orders.ShipCountry, Customers.CompanyName,
Customers.Address, Customers.City, Customers.Region,
Customers.PostalCode, Customers.Country
FROM Customers INNER JOIN (Orders INNER JOIN
Orders AS Orders_1
ON Orders.CustomerID = Orders_1.CustomerID)
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Orders.CustomerID, Orders.OrderDate,
Orders.OrderID, Orders.EmployeeID,
Orders.RequiredDate, Orders.ShippedDate,
Orders.ShipVia, Orders.Freight, Orders.ShipName,
Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion,
Orders.ShipPostalCode, Orders.ShipCountry,
Customers.CompanyName, Customers.Address,
Customers.City, Customers.Region,
Customers.PostalCode, Customers.Country
HAVING (((Orders.OrderDate)
=Max([Orders_1].[OrderDate])));

which, in Query Datasheet View, looks like this (at the beginning):

Customer Order Date Order ID ...
---------------------------- ----------- --------
Alfreds Futterkiste 09-Apr-1998 11011 ...
Ana Trujillo Emparedados ... 04-Mar-1998 10926 ...
Antonio Moreno Taquería 28-Jan-1998 10856 ...
...

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Klatuu]

Could be an order number if a date is note available. In fact it would be
better because it may be possilbe there would be two orders for the same
customer on the same date.

i have a one to many relationship for a customers and orders table. how do i
get the last entered order for each customer

Unless you have some field in each record in [orders] indicating the
date (and maybe time) of each order, you won't be able to determine
which was last entered.

Assuming you do have that, you can use a Query like the following one
(which targets Tables in the "Northwind Traders" database):

SQL view:

SELECT DISTINCTROW Orders.CustomerID, Orders.OrderDate,
Orders.OrderID, Orders.EmployeeID, Orders.RequiredDate,
Orders.ShippedDate, Orders.ShipVia, Orders.Freight,
Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
Orders.ShipRegion, Orders.ShipPostalCode,
Orders.ShipCountry, Customers.CompanyName,
Customers.Address, Customers.City, Customers.Region,
Customers.PostalCode, Customers.Country
FROM Customers INNER JOIN (Orders INNER JOIN
Orders AS Orders_1
ON Orders.CustomerID = Orders_1.CustomerID)
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Orders.CustomerID, Orders.OrderDate,
Orders.OrderID, Orders.EmployeeID,
Orders.RequiredDate, Orders.ShippedDate,
Orders.ShipVia, Orders.Freight, Orders.ShipName,
Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion,
Orders.ShipPostalCode, Orders.ShipCountry,
Customers.CompanyName, Customers.Address,
Customers.City, Customers.Region,
Customers.PostalCode, Customers.Country
HAVING (((Orders.OrderDate)
=Max([Orders_1].[OrderDate])));

which, in Query Datasheet View, looks like this (at the beginning):

Customer Order Date Order ID ...
---------------------------- ----------- --------
Alfreds Futterkiste 09-Apr-1998 11011 ...
Ana Trujillo Emparedados ... 04-Mar-1998 10926 ...
Antonio Moreno Taquería 28-Jan-1998 10856 ...
...

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.