How do I sort the an autonumber field to show only the highest va.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am doing a database to generate invoices. Each record is identified by an
auto number field. I want to sort in a query so that only the LAST record is
displayed...(the highest number generated by the autonumber field) How do I
do this???
 
You want to show only the highest InvoiceID number for each client?

1. Create a query into your Client and Invoice tables.

2. Depress the Total button on the toolbar.
Access adds a Total row to the grid.

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

4. Drag Invoice.InvoiceID into the grid.
In the Total row, choose Max under this field.

The query returns the maxiumum invoice number for each client.

If you need an example, open the Northwind sample database, create a new
query, paste this in, and then switch it to design view:

SELECT Customers.CustomerID,
Max(Orders.OrderID) AS MaxOfOrderID
FROM Customers LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID;
 
You might use a query whose SQL looks something like this:

SELECT TOP 1
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your AutoNumber Field] DESC

Be aware that AutoNumber fields are not guaranteed to be increasing or
gapless.

Perhaps a more direct way of finding the "last" invoice would be to add a
Date/Time field to your table with a default value of Now() (that is, the
current date and time), and then order by this field in a query like the one
above. Still, though, you face issues like:

1. Is the Date/Time field allowed to be changed? If not, preventing it
from being changed is somewhat non-trivial.

2. The precision of a Date/Time field is not unlimited. If you insert
enough records at once (for example, using an Append query), values will be
duplicated.

Making gapless sequence numbers is non-trivial.
 
Back
Top