Returning highest number in a row 1

[newbilko]

Hi,

This may be a common question.

I am creating a library database for a large number of data CD's. The catalogue uses a simple disk numbering system, i.e 1,2,3......n.

I want to be able to run a query that tells the user what the next disk number should be (as the disks are often not entered into the database in order).

It should be possible to search the database for the highest number and then add 1 to that number.

Any ideas how this can be achieved?

Many Thanks

 

[PHV]

SELECT 1+Max([Disk number]) AS NextNumber FROM yourTable

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[newbilko]

Thanks!!
Thats got it.

 

[newbilko]

Following on from this how can the above solution be applied to 2 tables. I.e. Find the highest value accross two tables?

Thanks

 

[newbilko]

In case anyone is interested a friend provided me with this solution for returning the highest value (plus 1) in a list of numbers in 2 different tables. (Collumn with numbers (numlist) has the same name in both tables)

Solution:

SELECT 1+Max(numlist) AS HighestValue
FROM [SELECT numlist FROM tbl_1 UNION SELECT numlisr FROM tbl_2]. AS qry_union;

Thanks to John for this.