Last Entered

  • Thread starter Thread starter Gary B
  • Start date Start date
G

Gary B

I have a table as follows:

OrderID(AutoIncPriKey), CustID(ForeignKey), Order Description

I want to know the query that would limit this table's data to the last
Entered Order for each CustID

Thank you
 
Gary

Define "last". Your definition probably is not the same as Access's.

Unless you have a date/time field in that table, I don't know of a reliable,
consistent way to get that.

And I'm not sure why/how you would use a query to "limit this table's
data" -- do you mean you want only to see the most recently-entered Order?

Regards

Jeff Boyce
<Office/Access MVP>
 
Unless you have a date/time field in that table, I don't know of a
reliable, consistent way to get that.
Click to expand...

The primarykey of the table is AutoInc, so the larger the number, the more
recent the entry.
And I'm not sure why/how you would use a query to "limit this table's
data" -- do you mean you want only to see the most recently-entered Order?
Click to expand...

Yes, but the most recently entered order for each Customer
 
Open the query grid. Click the totals button (the sum character on the
toolbar)
Drag CustomerID and OrderID to the grid. Change "Group by" under OrderID to
Max.

Save the query.

Now, open a new query. Drag your query to the grid, and the existing
Table(s) to the grid.

Drag CustomerID from your new query to the CustomerID that's related to
Orders in your table(s), and OrderID to OrderID.

Run the query. You could also do this with a subselect query if that's the
way that makes more sense to you (WHERE ORDERID IN (SELECT Max(ORDERID) FROM
Orders Where CustomerID=Thistable.CustomerID)).

HTH;

Amy
 
Gary

That would rely on the assumption that Access AutoNumbers are sequential ...
and there are conditions under which this is NOT so. You'd be much safer to
use a date/time field...

Amy offers a way to query...

Regards

Jeff Boyce
<Office/Access MVP>
 
Back
Top