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 ( 8 ) |

Send |

del.icio.us |

Digg It! |

Linking Blogs | 5457 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

[Add Comment]

Select Last Number In Access

Archives By Subject

Recent Comments

Recent Entries