the ultimate Query Challenge

[saleemMSMS]

This is what i want to do
consider the following table
Books (BookID, Title, Price, NoOfPages)
the format of the BookID is "BXXX" (i.e "B" Followed by 3 Numbers)
i want to get the latest BookID and get the Number part out of it. how can i do this ?

BookID Title Price NoOfPages
===== =============== ==== =======
B101 Java Programming 55 200
B102 VB.NET 67 230
B103 Access Step by Step 56 200

so what i need is to take the latest added record (in this case, B103) the extract the number part (i.e to get 103).

please tell me how to do. its not compulsory to only use Queries.. even VBA will do.. or a mixture..

please answer....

thanx

 

[Access2007User]

SELECT Last(Table1.bookid) AS LastOfbookid, Last(Right([bookid],3)) AS b
FROM Table1;


something like that for your "ultimate query challenge".

 

[saleemMSMS]

ok lets say i want to add the prefix 'C' to the result... how can i do that ?

 

[georgedwilkinson]

BTW, Last() is going to give you inconsistent results...it may be right some times but could just as easily go wrong. If you want the very last record to be entered to be returned EVERY time over the years, you need to use a sequence or a date entered field in your table. What happens if you enter 10,000 records in your table, delete 4,000, and then enter 5 more? Will Last() provide the correct record? Maybe and maybe not. The database does not have to conform to giving you answers in the order you gave it the data.

In order to add a "C" (or any other text) to the beginning of another value, just concatenate them. Something like this:

select 'C' & MyValue
from MyTable

This seems like a bad idea right off but there are some valid reasons for doing it.