Append query with incremental numbering of ID field (not autonumber). How? 
Author Message
 Append query with incremental numbering of ID field (not autonumber). How?

I bet his reason is the same as mine.  Your are not supposed to use the
Auto-Number data type for primary keys.

I have the same problem, and while *I* can call code to get around this
problem, the USERS of the database cannot (unless they are programmers too
<g>)  So the simple append queries that they've created to save data entry
time, no longer work because of the missing data in the key field.

I've received some tips that invovle using the Rnd() function, but I would
really like the numbers to be in consecutive order.

Quote:

>Simon,
>Any reason for not wanting to use AutoNumber?

>- Steve.



>> I'm sure it is simple. I want to append some records to a file that has
an
>> integer primary key.

>> I know how to get the Dmax of the ID as a starting point.

>> Without using the built in autonumber type, how can I append the fields
>> using an append query that then increments the ID field by 1 with each
new
>> record.

>> If I have to use code it would not be the end of the world. But it would
>be
>> a lot slower.

>> Any thoughts?

>> TIA - Simon



Sat, 24 Aug 2002 03:00:00 GMT  
 Append query with incremental numbering of ID field (not autonumber). How?
On Tue, 7 Mar 2000 20:33:40 -0500, "James M. Parker"

Quote:

>I bet his reason is the same as mine.  Your are not supposed to use the
>Auto-Number data type for primary keys.

Balderdash. I - and many other developers - do so all the time.
That's what they're FOR.

Is this that reference in the _Access Bible_? If so, it's simply
wrong.

John W. Vinson[MVP]    http://www.WysardOfInfo.Com
Remove ^ from EMail address to reply



Sat, 24 Aug 2002 03:00:00 GMT  
 Append query with incremental numbering of ID field (not autonumber). How?
I'm sure it is simple. I want to append some records to a file that has an
integer primary key.

I know how to get the Dmax of the ID as a starting point.

Without using the built in autonumber type, how can I append the fields
using an append query that then increments the ID field by 1 with each new
record.

If I have to use code it would not be the end of the world. But it would be
a lot slower.

Any thoughts?

TIA - Simon



Sun, 25 Aug 2002 03:00:00 GMT  
 Append query with incremental numbering of ID field (not autonumber). How?
Simon,
Any reason for not wanting to use AutoNumber?

- Steve.


Quote:
> I'm sure it is simple. I want to append some records to a file that has an
> integer primary key.

> I know how to get the Dmax of the ID as a starting point.

> Without using the built in autonumber type, how can I append the fields
> using an append query that then increments the ID field by 1 with each new
> record.

> If I have to use code it would not be the end of the world. But it would
be
> a lot slower.

> Any thoughts?

> TIA - Simon



Sun, 25 Aug 2002 03:00:00 GMT  
 Append query with incremental numbering of ID field (not autonumber). How?


Quote:
> ...  Your are not supposed to use the
> Auto-Number data type for primary keys.

James,
This is the first I have heard of this!  Where did you find this out?  I was
so alarmed when I read your post, that I resorted to having a look at the
way it was done in the infamous Northwind sample database.  You'll probably
be surprised to learn that the guys from Microsoft have got the primary key
in nearly all of the tables as AutoNumber!

Regards,
Steve.



Sun, 25 Aug 2002 03:00:00 GMT  
 Append query with incremental numbering of ID field (not autonumber). How?
Actually, the reason is because I am appending to a linked foxpro 2.6 table
field that already has a set of ID numbers before it.

I have also had trouble with make-table queries when there is more that one
auto-number.

And finally (and I am sure there are others), I sometimes have the user
rebuild a table with tens of thousands of records hundreds of times (don't
ask why - fault of my predecessor in his reliance on autonumbers) a month.
Call me fussy, but when you get into the millions (as a long integer) with
Access you know at some point you are going to get grief. I'd rather not
leave that to my successor. to deal with.

So any ideas on the subject would be appreciated. Thanks.

- Simon K.


Quote:
> Simon,
> Any reason for not wanting to use AutoNumber?

> - Steve.



> > I'm sure it is simple. I want to append some records to a file that has
an
> > integer primary key.

> > I know how to get the Dmax of the ID as a starting point.

> > Without using the built in autonumber type, how can I append the fields
> > using an append query that then increments the ID field by 1 with each
new
> > record.

> > If I have to use code it would not be the end of the world. But it would
> be
> > a lot slower.

> > Any thoughts?

> > TIA - Simon



Sun, 25 Aug 2002 03:00:00 GMT  
 Append query with incremental numbering of ID field (not autonumber). How?
Simon-

See comments inserted below...

--
John Viescas
author, "Running Microsoft Access 2000"
http://www.viescas.com/

Quote:
> Actually, the reason is because I am appending to a linked foxpro 2.6
table
> field that already has a set of ID numbers before it.

Ah, the plot thickens.  Are you using the FoxPro data directly, or are you
just linking to it to grab snapshots for this Access application?

Quote:

> I have also had trouble with make-table queries when there is more that
one
> auto-number.

You cannot define more than one Autonumber field per table.  What do you
mean by "more than one autonumber?"

Quote:

> And finally (and I am sure there are others), I sometimes have the user
> rebuild a table with tens of thousands of records hundreds of times (don't
> ask why - fault of my predecessor in his reliance on autonumbers) a month.
> Call me fussy, but when you get into the millions (as a long integer) with
> Access you know at some point you are going to get grief. I'd rather not
> leave that to my successor. to deal with.

Reliance on autonumbers should have nothing to do with forcing you to
rebuild tables with "tens of thousands of records."  Why do you think you
have to do this?


Sun, 25 Aug 2002 03:00:00 GMT  
 Append query with incremental numbering of ID field (not autonumber). How?
John,

Since you (and James) appear to have some expertise on this subject, perhaps
you can help me out.

I have been using an Autonumber as the primary key in a variety of the
tables in our Access database.  Today I realized that some of the records
had been assigned duplicate autonumbers without my being aware of it (the
key field was mistakenly indexed so as to allow duplicate records, so this
didn't get discovered until we started getting back strange results on our
queries).

Now when I try to add a new record to a particular table with an autonumber,
the autonumber that Access attempts to assign is one that has already been
used.  In this particular case, the max id in the table is currently 92775,
and the number that gets assigned starts somewhere around 92423.  Because
the table is now indexed correctly, if I actually try to leave the record to
commit it to the database, Access immediately complains and I can't
continue.

I have tried compacting and repairing the database with no real difference
in the process. The second time I repaired, it tried to give the new record
an id of 92422.  Another post I saw mentioned problems if any of the records
had 0 or -1 as one of the values in the autonumber field...that is not the
case in this table.

At this point the most obvious guaranteed solution is to create our own
autonumber, which of course would have it's own set of issues to deal with
in a multi-user environment...which means that is a solution I really want
to avoid.  I really would just like autonumbers to work for me all the time
in a way that I could trust I would always get good, unique numbers.

Any thoughts?

David Cater
Police Central, Inc.

Quote:

>On Tue, 7 Mar 2000 20:33:40 -0500, "James M. Parker"

>>I bet his reason is the same as mine.  Your are not supposed to use the
>>Auto-Number data type for primary keys.

>Balderdash. I - and many other developers - do so all the time.
>That's what they're FOR.

>Is this that reference in the _Access Bible_? If so, it's simply
>wrong.

>John W. Vinson[MVP]    http://www.WysardOfInfo.Com
>Remove ^ from EMail address to reply



Sun, 25 Aug 2002 03:00:00 GMT  
 Append query with incremental numbering of ID field (not autonumber). How?
On Wed, 8 Mar 2000 14:10:57 -0500, "David Cater"

Quote:

>Now when I try to add a new record to a particular table with an autonumber,
>the autonumber that Access attempts to assign is one that has already been
>used.

This is a bug I've heard of a couple of times but never seen.  I'll go
check out the MSKB (you might want to as well) - but it's clearly a
bug, not a routine way that autonumbers work!

To solve the problem I'd suggest running an Append query to add a new
record to your table, explicitly appending a number into the
autonumber field that is one less than where you want the
autonumbering to start up again.  Then delete the record and compact.

Next step would be to create a new, empty database and Import all your
tables and other objects into it.  There may be some subtle corruption
in the system tables.

I'm sorry you're having trouble with the Autonumber; I just hadn't
focused on this (apparently rare but very real) bug.

John W. Vinson[MVP]    http://www.WysardOfInfo.Com
Remove ^ from EMail address to reply



Sun, 25 Aug 2002 03:00:00 GMT  
 Append query with incremental numbering of ID field (not autonumber). How?
One way out of the quagmire may be to set the autonumber property New Values to
random instead of sequential.
This won't take care of any current duplicates, but it should prevent future
duplicates.

If you don't need the sequential effect this also has some side benefits in
allowing you to roll a home-grown version of data integration.  I've used it to
allow me to integrate data from two datafiles where the user "accidentally"
started a second version of the data and then realized after a couple of days
that some of their records had disappeared.

The details of the story are too long to relate so don't ask.  

Quote:

> John,

> Since you (and James) appear to have some expertise on this subject, perhaps
> you can help me out.



Sun, 25 Aug 2002 03:00:00 GMT  
 Append query with incremental numbering of ID field (not autonumber). How?
WHEW, indeed!  Keep in mind that an AutoNumber field is actually a long
integer that can go up to 2,147,483,647.  If you don't actually care what
the "next" number is as long as the "next" row is one more than the previous
row, you can run a Delete followed by an Append to get numbered rows.  Even
if you go past 2,147,483,647, it wraps around to minus 2,147,483,648.  If
you empty the table(s) and compact the database occasionally, the number
returns to 1.  Even if you don't do the reset, assuming an average of 20,000
rows per delete/append, you would need to do it 107,374 times to exceed the
upper limit of Autonumber.  If you do that an average of 4 times per day,
you won't run out of numbers for 73 years!  Given the limit of 1 gig for an
mdb file, you'll probably need to compact 'way before then!

--
John Viescas
author, "Running Microsoft Access 2000"
http://www.viescas.com/

Quote:
> There are reasons - see my comments below:

[snip]
Quote:

> Whew!

> Thanks - hope this makes more sense.



Sun, 25 Aug 2002 03:00:00 GMT  
 Append query with incremental numbering of ID field (not autonumber). How?
There are reasons - see my comments below:


Quote:
> Simon-

> See comments inserted below...

> --
> John Viescas
> author, "Running Microsoft Access 2000"
> http://www.viescas.com/


> > Actually, the reason is because I am appending to a linked foxpro 2.6
> table
> > field that already has a set of ID numbers before it.

> Ah, the plot thickens.  Are you using the FoxPro data directly, or are you
> just linking to it to grab snapshots for this Access application?

Both - unfortunately, our group uses a medical practice software (through
many of our practices) that is built in Foxpro 2.6. Very easy user
interface. Terrible reports. So we built our own set of reports with Access.

However, there is a limited functionality to the application written by the
3rd party vendor. We need at times to insert values back into the Foxpro
database so that users can get the benefits of some things we can't get the
vendor to develop.

Quote:
> > I have also had trouble with make-table queries when there is more that
> one
> > auto-number.

> You cannot define more than one Autonumber field per table.  What do you
> mean by "more than one autonumber?"

When I grab several related tables to build a make table query - and these
queries can get pretty nested - but we found it orders of magnitude faster
and more efficient than code in our current application (not the one
referred to above) - then the autonumbers seemed to follow through to the
next query / maketable, and so on.

Since I suspect you're gonna ask it, so I might as well answer it - I use so
many nested queries and maketables because I found that if I only used
queries throughout then
a)    some large queries were reused in the chain, and so a maketable was
more efficient; and
b)    I eventually encountered Access memory issues (on a 32MB Pentium) and
eventually resolved this only by installing maketables along the way.
Nothing else would solve it.
c)    If you knew the application - and the original limitations of the
design - you would probably conclude there was no other way. I'm in the
process of starting from scratch with the lessons of that application. But
it is what it is now.

Quote:

> > And finally (and I am sure there are others), I sometimes have the user
> > rebuild a table with tens of thousands of records hundreds of times
(don't
> > ask why - fault of my predecessor in his reliance on autonumbers) a
month.
> > Call me fussy, but when you get into the millions (as a long integer)
with
> > Access you know at some point you are going to get grief. I'd rather not
> > leave that to my successor. to deal with.

> Reliance on autonumbers should have nothing to do with forcing you to
> rebuild tables with "tens of thousands of records."  Why do you think you
> have to do this?

Again the reason is the limitations of the original design. A flat
timesheet -related table - every day of the week was represented
datMonStartTime datMonEndTime datMonBreakTime and many other Mon related
fields and the same for each day of the week was used.

However we needed to do several levels of (to us, at least) complex
processing to achieve certain analysis that required (and please trust me on
this - you know all the complexities of time-oriented analysis, I'm sure)
"one day, one record". No problem - if I didn't already have a lot of
related forms etc using the old model.

So I made a resources decision to simply let the existing flat timesheet
stay in existance, change all the tables I could reasonably easily change
and let the user update (with one click once a week) the 3rd normal form
tables "one day, one record". This works great and solved all our problems.

For several reasons we found it more efficient to delete and rebuild the
normalised tables each time (usually only once a week) than to edit and
update them.

As we were wedded (resource limitations, again) to the autonumber indices,
and so created the problem described above.

Whew!

Thanks - hope this makes more sense.

- Show quoted text -



Mon, 26 Aug 2002 03:00:00 GMT  
 Append query with incremental numbering of ID field (not autonumber). How?
Thanks. That still doesn't help me with the linked Foxpro tables. Whether or
not it is necessary, is it possible to have an incrementing non-autonumber
function in an append query. Maybe I just answered my own question.


Quote:
> WHEW, indeed!  Keep in mind that an AutoNumber field is actually a long
> integer that can go up to 2,147,483,647.  If you don't actually care what
> the "next" number is as long as the "next" row is one more than the
previous
> row, you can run a Delete followed by an Append to get numbered rows.
Even
> if you go past 2,147,483,647, it wraps around to minus 2,147,483,648.  If
> you empty the table(s) and compact the database occasionally, the number
> returns to 1.  Even if you don't do the reset, assuming an average of
20,000
> rows per delete/append, you would need to do it 107,374 times to exceed
the
> upper limit of Autonumber.  If you do that an average of 4 times per day,
> you won't run out of numbers for 73 years!  Given the limit of 1 gig for
an
> mdb file, you'll probably need to compact 'way before then!

> --
> John Viescas
> author, "Running Microsoft Access 2000"
> http://www.viescas.com/


> > There are reasons - see my comments below:

> [snip]

> > Whew!

> > Thanks - hope this makes more sense.



Wed, 28 Aug 2002 03:00:00 GMT  
 Append query with incremental numbering of ID field (not autonumber). How?
Simon-

We've been so busy trying to come up with alternative solutions to your
FoxPro problem that I lost sight of your original simple request.

The answer is YES, but you have to build a couple of VBA functions.

Option Explicit
Dim lngCounter As Long

Public Function ResetCount()
' This function gets called *once* by the append or Make Table query
' You can change this code to start with any value -- including doing
' a DLookup to a table

    lngCounter = 0

End Function

Public Function GetCounter (varSomeField As Variant) As Long
' This function gets called for each row in the query
'  It adds one to the counter and returns it for use as a
'  custom "autonumber"

    lngCounter = lngCounter + 1
    GetCounter = lngCounter
End Function

You can paste this code into Northwind, then run this query to see how it
works:

SELECT TOP 5 ResetCount() AS x, GetCounter([CustomerID]) AS [Counter],
Customers.CustomerID, Customers.CompanyName
FROM Customers;

The result is:

x    Counter Customer ID Company Name
<null>  1      ALFKI            Alfreds Futterkiste
<null>   2     ANATR          Ana Trujillo Emparedados y helados
<null>   3     ANTON          Antonio Moreno Taquera
<null>   4     AROUT          Around the Horn
<null>   5     BERGS         Berglunds snabbk?p
<null>   6     BLAUS          Blauer See Delikatessen
<null>   7     BLONP          Blondel pre et fils
<null>   8     BOLID            Blido Comidas preparadas
<null>   9     BONAP          Bon app'
<null>  10    BOTTM          Bottom-Dollar Markets

The trick is Access calls a function with no argument only once when it
first starts to process the query.  When you call a function with one of the
columns in the underlying tables, it calls the function for each row.
Caution:  If you do things like add sorting criteria, you may get a
different sequence of final numbers because JET touches each input row more
than once.  You'll still get ascending numbers in sequence.

--
John Viescas
author, "Running Microsoft Access 2000"
http://www.viescas.com/

Quote:
> Thanks. That still doesn't help me with the linked Foxpro tables. Whether
or
> not it is necessary, is it possible to have an incrementing non-autonumber
> function in an append query. Maybe I just answered my own question.



> > WHEW, indeed!  Keep in mind that an AutoNumber field is actually a long
> > integer that can go up to 2,147,483,647.  If you don't actually care
what
> > the "next" number is as long as the "next" row is one more than the
> previous
> > row, you can run a Delete followed by an Append to get numbered rows.
> Even
> > if you go past 2,147,483,647, it wraps around to minus 2,147,483,648.
If
> > you empty the table(s) and compact the database occasionally, the number
> > returns to 1.  Even if you don't do the reset, assuming an average of
> 20,000
> > rows per delete/append, you would need to do it 107,374 times to exceed
> the
> > upper limit of Autonumber.  If you do that an average of 4 times per
day,
> > you won't run out of numbers for 73 years!  Given the limit of 1 gig for
> an
> > mdb file, you'll probably need to compact 'way before then!

> > --
> > John Viescas
> > author, "Running Microsoft Access 2000"
> > http://www.viescas.com/


> > > There are reasons - see my comments below:

> > [snip]

> > > Whew!

> > > Thanks - hope this makes more sense.



Wed, 28 Aug 2002 03:00:00 GMT  
 Append query with incremental numbering of ID field (not autonumber). How?
Merci!


Quote:
> Simon-

> We've been so busy trying to come up with alternative solutions to your
> FoxPro problem that I lost sight of your original simple request.

> The answer is YES, but you have to build a couple of VBA functions.

> Option Explicit
> Dim lngCounter As Long

> Public Function ResetCount()
> ' This function gets called *once* by the append or Make Table query
> ' You can change this code to start with any value -- including doing
> ' a DLookup to a table

>     lngCounter = 0

> End Function

> Public Function GetCounter (varSomeField As Variant) As Long
> ' This function gets called for each row in the query
> '  It adds one to the counter and returns it for use as a
> '  custom "autonumber"

>     lngCounter = lngCounter + 1
>     GetCounter = lngCounter
> End Function

> You can paste this code into Northwind, then run this query to see how it
> works:

> SELECT TOP 5 ResetCount() AS x, GetCounter([CustomerID]) AS [Counter],
> Customers.CustomerID, Customers.CompanyName
> FROM Customers;

> The result is:

> x    Counter Customer ID Company Name
> <null>  1      ALFKI            Alfreds Futterkiste
> <null>   2     ANATR          Ana Trujillo Emparedados y helados
> <null>   3     ANTON          Antonio Moreno Taquera
> <null>   4     AROUT          Around the Horn
> <null>   5     BERGS         Berglunds snabbk?p
> <null>   6     BLAUS          Blauer See Delikatessen
> <null>   7     BLONP          Blondel pre et fils
> <null>   8     BOLID            Blido Comidas preparadas
> <null>   9     BONAP          Bon app'
> <null>  10    BOTTM          Bottom-Dollar Markets

> The trick is Access calls a function with no argument only once when it
> first starts to process the query.  When you call a function with one of
the
> columns in the underlying tables, it calls the function for each row.
> Caution:  If you do things like add sorting criteria, you may get a
> different sequence of final numbers because JET touches each input row
more
> than once.  You'll still get ascending numbers in sequence.

> --
> John Viescas
> author, "Running Microsoft Access 2000"
> http://www.viescas.com/


> > Thanks. That still doesn't help me with the linked Foxpro tables.
Whether
> or
> > not it is necessary, is it possible to have an incrementing
non-autonumber
> > function in an append query. Maybe I just answered my own question.



> > > WHEW, indeed!  Keep in mind that an AutoNumber field is actually a
long
> > > integer that can go up to 2,147,483,647.  If you don't actually care
> what
> > > the "next" number is as long as the "next" row is one more than the
> > previous
> > > row, you can run a Delete followed by an Append to get numbered rows.
> > Even
> > > if you go past 2,147,483,647, it wraps around to minus 2,147,483,648.
> If
> > > you empty the table(s) and compact the database occasionally, the
number
> > > returns to 1.  Even if you don't do the reset, assuming an average of
> > 20,000
> > > rows per delete/append, you would need to do it 107,374 times to
exceed
> > the
> > > upper limit of Autonumber.  If you do that an average of 4 times per
> day,
> > > you won't run out of numbers for 73 years!  Given the limit of 1 gig
for
> > an
> > > mdb file, you'll probably need to compact 'way before then!

> > > --
> > > John Viescas
> > > author, "Running Microsoft Access 2000"
> > > http://www.viescas.com/


> > > > There are reasons - see my comments below:

> > > [snip]

> > > > Whew!

> > > > Thanks - hope this makes more sense.



Fri, 30 Aug 2002 03:00:00 GMT  
 
 [ 16 post ]  Go to page: [1] [2]

 Relevant Pages 

1. Incremental numbering within field-not autonumber!

2. Auto-numbering records in an Append Query without using an autonumber field

3. Append Queries-calculating "incremental numbers"

4. Appending to Autonumber field in Append Query?

5. Converting Number ID to AutoNumber ID

6. Make table qry that will reatain unique ID numbers in an autonumber field

7. Adding a field to a query with incremental numbering

8. Returning a Unique ID (Autonumber field) in a query

9. Programming Incremental number that function like AutoNumber but isn't

10. Programming Incremental number that function like AutoNumber but isn't


 
Powered by phpBB® Forum Software © phpBB Group