How do I query a recordset to return only the last records?

[Guest]

Hi. I have an Access 2000 database with two tables (customers and sales). The
table "sales" contains a field (CustomerID) which is linked to the CustomerID
in the "customers" table. I want to create a query where I get a list of all
my last sales for each customer. The way I currently do this is sort the
"sales table" by "CustomerID" and then "sales date", copy to Excel and then
manually delete everything except the last sale for each customer. Does
anyone know how to do this using an access query? I appreciate any help!!!
Thanks!

 

[Allen Browne]

1. Create a query into this table.

2. Depress the Total button (Toobar icon.)
Access adds a Total row to the grid.

3. Drag CustomerID into the grid.
In the Total row under this field, accept Group By.

4. Add the Sales Date field into the grid.
In the Total row under this field, choose Max.

 

[Guest]

Allen-

Thanks! When I do that it works, but I can't display the other fields in
the"sales" table.. For example, in the "sales" table there are fields in
addition to date, such as Description, Amount, etc., that i would also like
to display. When I put the other data fields in the query design view, I have
to also put "Group By" in the total row for these. Now instead of the last
sale per customer ID, I get multiple sales per customer ID.

Any ideas?
Thanks.

 

[John Vinson]

Hi. I have an Access 2000 database with two tables (customers and sales). The
table "sales" contains a field (CustomerID) which is linked to the CustomerID
in the "customers" table. I want to create a query where I get a list of all
my last sales for each customer. The way I currently do this is sort the
"sales table" by "CustomerID" and then "sales date", copy to Excel and then
manually delete everything except the last sale for each customer. Does
anyone know how to do this using an access query? I appreciate any help!!!
Thanks!

You can use a Subquery to select only the most recent sales date.

Create a Query by joining Customers and Sales.

On the Criteria line under [Sales Date] type

=(SELECT Max([Sales Date]) FROM [Sales] AS X WHERE X.CustomerID =
Customers.CustomerID)

This will create a alias name X for the Sales table; find the maximum
date for each customer; and filter the query so that only that sale is
shown.


John W. Vinson[MVP]

 

[Van T. Dinh]

See The Access Web article:

http://www.mvps.org/access/queries/qry0020.htm