473,326 Members | 2,061 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

How To Find Next Autonumber Value

I'm using the following SQL statement to find the next highest
autonumber value in a table where "CDUGActID is the autonumber field
in the "CDUGActuals" table:

SELECT CDUGActuals.CDUGActID, CDUGActuals.ActualTonnes, [CDUGActID]+1
AS NextID
FROM CDUGActuals;

This works well as long as no autonumber values have been skipped due
to deletions or cancelled entries. Is there a simple change I can
make so that the "NextID" field will show the next highest autonumber
value irrespective of if autonumber values have been skipped?

I thought of using a dedicated numbering field and populating it
sequentially using the Dmax function, but realized that this would not
be suitable because I may need to apply this query to a filtered list
from the table in which case both the autonumber field and the
dedicated numbering field would not be sequential.
Feb 12 '08 #1
6 8010
Wayne wrote:
I'm using the following SQL statement to find the next highest
autonumber value in a table where "CDUGActID is the autonumber field
in the "CDUGActuals" table:

SELECT CDUGActuals.CDUGActID, CDUGActuals.ActualTonnes, [CDUGActID]+1
AS NextID
FROM CDUGActuals;

This works well as long as no autonumber values have been skipped due
to deletions or cancelled entries. Is there a simple change I can
make so that the "NextID" field will show the next highest autonumber
value irrespective of if autonumber values have been skipped?
Not that I know of.

Let's say I have a table Customers with CustID (autonum) and CustName.
What's CustID = 4? Who's 4? I'm interested in the name. CustID is the
field I use to reference a record when linked to other tables.

If I needed something of your needs I'd consider creating another field.
Like NextCustID. Make it a Long, unique value. No stuff it with a
number that moves sequentially and use that as your reference number.

Or in a query you could get the count of autonums less/equal to your ID
+ 1. Ex:
NextID : Dcount("*","TableName","ID <= " & [ID]) + 1

The other option is to remove the Autonumber field and make it a Long
and create a routine to save your sequential number.

Turn Around
http://www.youtube.com/watch?v=JYB_hvVBFYc
>
I thought of using a dedicated numbering field and populating it
sequentially using the Dmax function, but realized that this would not
be suitable because I may need to apply this query to a filtered list
from the table in which case both the autonumber field and the
dedicated numbering field would not be sequential.
Feb 12 '08 #2
Wayne <cq*******@volcanomail.comwrote:
>I'm using the following SQL statement to find the next highest
autonumber value in a table where "CDUGActID is the autonumber field
in the "CDUGActuals" table:

SELECT CDUGActuals.CDUGActID, CDUGActuals.ActualTonnes, [CDUGActID]+1
AS NextID
FROM CDUGActuals;
Why are you doing this? What is your objective?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Feb 12 '08 #3

Why are you doing this? *What is your objective?

Tony
--
Tony Toews, Microsoft Access MVP
* *Please respond only in the newsgroups so that others can
read the entire thread of messages.
* *Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm
* *Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
Thanks for the reply. The objective was to grab the previous record in
a query. After lot of trawling the newsgroup I'm now persuing another
method.
Feb 13 '08 #4
"Wayne" <cq*******@volcanomail.comwrote in message
news:e1**********************************@i29g2000 prf.googlegroups.com...
>Why are you doing this? What is your objective?
Tony
Tony Toews, Microsoft Access MVP
>Thanks for the reply. The objective was to grab the previous record in
a query. After lot of trawling the newsgroup I'm now persuing another
method.
While trying to determine the next autonumber is usually not a worthwhile
pursuit. It does raise the question why it can't be easily done. Access must
keep that number 'somewhere' internally.....right?
Fred Zuckerman
Feb 13 '08 #5
Wayne <cq*******@volcanomail.comwrote:
>Why are you doing this? *What is your objective?

Thanks for the reply. The objective was to grab the previous record in
a query. After lot of trawling the newsgroup I'm now persuing another
method.
Yes, I realize that is your immediate objective. But why are you doing this? What
business logic are you trying to perform?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Feb 13 '08 #6
"Fred Zuckerman" <Zu********@sbcglobal.netwrote in
news:p_******************@newssvr14.news.prodigy.n et:
While trying to determine the next autonumber is usually not a
worthwhile pursuit. It does raise the question why it can't be
easily done. Access must keep that number 'somewhere'
internally.....right?
Surely there's a way to get at the seed value (though it may be
available only through ADO and not DAO), but I don't know how it's
done.

But I also don't know *why* you'd ever want to know, since if you're
using Autonumbers, the values shouldn't be meaningful in the first
place. It's only if you're maintaining your own sequence that the
numbers can have meaning, and in that case, you should have no
trouble figuring out what the next one should be.

Dunno if it helps, but you can always do a non-equi join with two
copies of the table, having the join link on Autonumber =
Autonumber-1. Or you can do the join with a WHERE clause.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Feb 13 '08 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Phillip J. Allen | last post by:
Hi all, I have a table with an “autonumber” primary key field that also acts as a foreign key in 2 other tables. I would like to programmatically add a new record to the first table and the...
33
by: Lee C. | last post by:
I'm finding this to be extremely difficult to set up. I understand that Access won't manage the primary key and the cascade updates for a table. Fine. I tried changing the PK type to number and...
2
by: Ed Havelaar | last post by:
Hi, Hope someone can help. Here's the scenario: I have two MSAccess tables MainTab and SubTab. MainTab has an autonumber 'id' column as primary key. Subtab has this id column as a foreign...
35
by: Traci | last post by:
If I have a table with an autonumber primary key and 100 records and I delete the last 50 records, the next record added would have a primary key of 101. Is there any way to have the primary key...
4
by: yf | last post by:
A KB article "http://support.microsoft.com/default.aspx?scid=kb;en-us;209599" tells that the maximum number of records that a table may hold if the PRIMARY key data type is set to AUTONUMBER is...
4
by: Nathan Sokalski | last post by:
I am using ASP.NET to insert records into a Microsoft Access Database. My primary keys are of type Autonumber. However, because some of the tables have relationships I need to know the value of the...
4
by: timasmith | last post by:
I guess more the fool me for attempting with MS Access but if I execute select max(nbr) from mytable nextnbr = maxnbr + 1 insert into mytable (nextnbr...) multiple times - it appears the...
4
by: keri | last post by:
Hello again, While I try and solve the calender issue I also have another problem. I have a form & table (appointments) where the user records appointments in the future. The fields include...
1
by: VG | last post by:
Hi folks, I have procedure that I am using to assign ID number vs Autonumber. It is working fine but something going wrong with my form that I have to replace the form with old version to make...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.