Select Last Number In Access

Posted At : 07 March 2009 12:00 | Posted By : Jonny Shaw
Related Categories: SQL

I have recently been working on a ticketing system and i needed to know how to find they next number for the primary key. Took me a while to find out how to do it in access so i thought i would share it.

Here is this SQL that i used.

SELECT LAST([Tickets.TicketID])+1 AS TicketID FROM Tickets

I know that with many of the better DBMS they can return the generated keys, But from what i found you can not do this with access.

If you have found any easier methods then please feel free to share them.

Comments ( 12 ) |

Send |

del.icio.us |

Digg It! |

Linking Blogs | 5821 Views

Comments

[Add Comment]

Use an autonumber column. It gets set automatically instead of you getting it first.

# Posted By dfsaf | 07/03/09 13:57

I needed to insert to 2 tables at the same time. I needed to know the TicketID from the 1st table to use it in the second.
But as i said in the post MS access wont allow you to return generated keys.

# Posted By Jonny Shaw | 07/03/09 14:10

My question is simple, why MS Access?

Access is maybe better these days than it was many years ago. But there are far better databases to use than MS Access. Hell Coldfusion 8 comes with Derby and you can use other free DB's like mySQL or MS SQL Express to name a few.

It still amazes me why people still use MS Access, I see no good reason for it.

# Posted By Andrew Scott | 07/03/09 21:19

I was using it with a java application.
The only reason i used access was because it was a requirement in the specification.
I would have probably used oracle if it were up to me.

# Posted By Jonny Shaw | 08/03/09 23:24

If you use the autonumber column you can do a select max(id) from firsttable after you've done the insert to get the number you need for the second table, just do the insert and select inside a cftransaction

# Posted By dave | 06/04/09 19:26

I wasnt using coldfusion, i was using java

# Posted By Jonny Shaw | 06/04/09 19:36

Make one more column, insert a createuuid() into that column.. do a selection to get the id of the column where create_uuid = #the_uuid_you_just_inserted#.

Otherwise, you'll run into issues using this code when multiple people start using it.

# Posted By Todd Rafferty | 05/08/09 23:18

It will work fine if it is done in a transaction

# Posted By Jonny Shaw | 05/08/09 23:22

With the help of Rapidshare Search engine( http://www.rapidsharemix.com/trends/cz/08-04-2010.... ) you can search all rapid share files easily

# Posted By Reagan | 09/04/10 17:58

http://www.whatladybuys.com
http://www.whatladybuys.com
http://www.whatladybuys.com
http://www.whatladybuys.com
http://www.whatladybuys.com

# Posted By fendi shoes | 30/08/10 02:10

Free shipping buy coach handbags in coach outlet online,save up 76%,[url=http://www.coachhandbags-onsale.us/]coach handbags on sale[/url],2010 new style ugg snow boots cheap sale,[url=http://www.uggbootsallhere2.com/]cheap ugg boots[/url]

Nike air max 2010,air max 2009,air max shoes on sale.Buy nike air max shoes on http://www.nikeairmax-1.com low at $62,especially [url=http://www.nikeairmax-1.com/]air max 2010[/url],[url=http://www.nikeairmax-1.com/]air max 2009[/url],BOSE products include BOSE headphones,BOSE mobile in-ear, Bose earphones headphones [url=http://www.headphoneonsales.com/]cheap bose headphones[/url][url=http://www.headphoneonsales.com/]bose headphones[/url]

# Posted By nike air max shoes | 02/09/10 07:30

These four pairs are all from Sergio Rossi. And their color is all can match well with your wedding dress, and make you look more charming when you wear them,[url=http://www.mychristianlouboutinshoes.com/blog]christian louboutin shoes [/url],[url=http://www.mychristianlouboutinshoes.com/cheap-chr...]cheap christian louboutin[/url],[url=http://www.mychristianlouboutinshoes.com/christian...]christian louboutin boots[/url],[url=http://www.mychristianlouboutinshoes.com/christian...]christian louboutin pumps[/url],[url=http://www.mychristianlouboutinshoes.com/christian...]christian louboutin shoes[/url],[url=http://www.christian-louboutin-sale.us/]christian louboutin sale[/url],[url=http://www.christian-louboutin-sale.us/]cheap christian louboutin[/url]

# Posted By coach handbags | 02/09/10 07:30

[Add Comment]

Select Last Number In Access

Archives By Subject

Recent Comments

Recent Entries