By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,574 Members | 3,021 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,574 IT Pros & Developers. It's quick & easy.

Trying to find the biggst value in a table

P: 1

Ive got a Product table that has a productID.
This is incremented insdie the access table itself.
What im trying to do is find the biggest number in the table and output all the information in that row.

Now the biggest number keeps changing when a new product is entered. ie.
Product ID = 34 contains plugs.
if a new product is entered it will become
product id = 35 contains screws.

So how can i write a code that finds the largest by just looking into the biggest number?
Dec 14 '11 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 446
One way is to create a query selecting each of the fields from your Product table, then set Order By to ProductID Descending. Create a Form based on this query and the first item to be displayed will be the last one added. No code needed.

Alternatively you could use the DMax() function to find the largest ProductID. But what do you mean by 'output all the information in that row'? To where? a pop-up message window, a form or a report?

We need more info to take this further.
Dec 14 '11 #2

Expert Mod 15k+
P: 31,494
I'm going to assume for now that you are looking for a query that returns the data you requested. If you need other information then, as S7 says, you need to provide more information in the question.

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM   [Product]
  3. WHERE  ([ProductID] = SELECT Max([ProductID])
  4.                       FROM   [Product])
This uses a subquery to determine the max value, which is similar to using DMax() but where DMax() is a function the subquery is part of the SQL itself so can work more efficiently. Domain Aggregate functions like DMax() are of most use when used from within VBA code, although they certainly can be used within SQL too if required.
Dec 14 '11 #3

Post your reply

Sign in to post your reply or Sign up for a free account.