| Author |
Message |
|
Adrian Harriso
#1 / 15
|
 Help resetting counter field value? I want to be able to set a counter field value back to 1 - currently it has
reached 54 but I want to delete
all the records and start again at 1!
I've tried the tip supplied in the access help file using an append query
but this does not achieve want I want!
Any ideas
Sorry if posted to wrong newgroups!
Thanks
|
| Sun, 06 Aug 2000 03:00:00 GMT |
|
 |
|
Bill Serrah
#2 / 15
|
Help resetting counter field value? Adrian,
After deleting all of the records you must compact the database in order to
get the counter to reset. The only other way that I know of is to drop the
table and rebuild it.
Bill Serrahn
http://home.att.net/~Bill.Serrahn/
Quote:
>I want to be able to set a counter field value back to 1 - currently it has >reached 54 but I want to delete >all the records and start again at 1! >I've tried the tip supplied in the access help file using an append query
>but this does not achieve want I want!
>Any ideas
>Sorry if posted to wrong newgroups!
>Thanks
|
| Sun, 06 Aug 2000 03:00:00 GMT |
|
|
|
Andrew C. Madse
#3 / 15
|
Help resetting counter field value? Is the counter field an AutoNumber field? If it is delete your records and
then compact the database. That should do the trick.
Andrew
Quote:
>I want to be able to set a counter field value back to 1 - currently it has >reached 54 but I want to delete >all the records and start again at 1! >I've tried the tip supplied in the access help file using an append query
>but this does not achieve want I want!
>Any ideas
>Sorry if posted to wrong newgroups!
>Thanks
|
| Sun, 06 Aug 2000 03:00:00 GMT |
|
|
|
Dire Rave
#4 / 15
|
Help resetting counter field value? Compat the database after deleting the records.
Quote:
>I want to be able to set a counter field value back to 1 - currently it has >reached 54 but I want to delete >all the records and start again at 1! >I've tried the tip supplied in the access help file using an append query
>but this does not achieve want I want!
>Any ideas
>Sorry if posted to wrong newgroups!
>Thanks
|
| Sun, 06 Aug 2000 03:00:00 GMT |
|
|
|
Roger Man
#5 / 15
|
Help resetting counter field value? Delete everything then compact the database.
Quote: > I want to be able to set a counter field value back to 1 - currently it has > reached 54 but I want to delete > all the records and start again at 1! > I've tried the tip supplied in the access help file using an append query
> but this does not achieve want I want!
> Any ideas
> Sorry if posted to wrong newgroups!
> Thanks
|
| Sun, 06 Aug 2000 03:00:00 GMT |
|
|
|
Alden Streete
#6 / 15
|
Help resetting counter field value?
|
| Sun, 06 Aug 2000 03:00:00 GMT |
|
|
|
<ba..
#7 / 15
|
Help resetting counter field value? Assuming this is an autonumber , there is another way to reset the counter
without compacting.
delete all records, change the autonumber to a number, save the table,
change the field back to an autonumber.
regards,
Barry Matfield
Sagekey Software
www.SgaeKey.com
Quote:
>I want to be able to set a counter field value back to 1 - currently it has >reached 54 but I want to delete >all the records and start again at 1! >I've tried the tip supplied in the access help file using an append query
>but this does not achieve want I want!
>Any ideas
>Sorry if posted to wrong newgroups!
>Thanks
|
| Sun, 06 Aug 2000 03:00:00 GMT |
|
|
|
Kurt Mila
#8 / 15
|
Help resetting counter field value? Here's another way to do it without compacting:Copy the table. paste it into the
database window as table 1. Choose "Paste structure only". Delete the old
table. Rename the pasted table with the original table's name...
Sorta elementary, but so am I when it comes to this stuff.
Kurt
Quote:
> >I want to be able to set a counter field value back to 1 - currently it has > >reached 54 but I want to delete > >all the records and start again at 1! > >I've tried the tip supplied in the access help file using an append query
> >but this does not achieve want I want!
> >Any ideas
> >Sorry if posted to wrong newgroups!
> >Thanks
|
| Sun, 06 Aug 2000 03:00:00 GMT |
|
|
|
John A. Grossbohl
#9 / 15
|
Help resetting counter field value? Quote:
>Adrian, >After deleting all of the records you must compact the database in order to
>get the counter to reset. The only other way that I know of is to drop the
>table and rebuild it.
>>I want to be able to set a counter field value back to 1 - currently it has
>>reached 54 but I want to delete
>>all the records and start again at 1!
Simply delete that field from the table structure and then add it back in.... If you have that field as the key or part of an index you will have to delete the key first and rebuild the indexes afterwards. John
|
| Mon, 07 Aug 2000 03:00:00 GMT |
|
|
|
Don Leverto
#10 / 15
|
Help resetting counter field value? Hi Adrian.
Try this... it works for me.
Delete any relationships that this table is particpating in.
Delete the counter field in the table's design view.
Exit and save the table.
Re-open the table in design view, put the counter field back in.
Re-establish any relationships that you deleted above.
Everyone else suggesting deleting, compacting... I have never tried it but
will next time.
I'm only suggesting this cuz I know it works also.
Don
Quote: > I want to be able to set a counter field value back to 1 - currently it has > reached 54 but I want to delete > all the records and start again at 1! > I've tried the tip supplied in the access help file using an append query
> but this does not achieve want I want!
> Any ideas
> Sorry if posted to wrong newgroups!
> Thanks
|
| Mon, 07 Aug 2000 03:00:00 GMT |
|
|
|
George Diamantopoulo
#11 / 15
|
Help resetting counter field value? Compacting the database if enough to reset the counter. Just delete all
records and choose Compact Database from the Tools menu.
I have done it myself, so I know it works.
Good luck...
--
--------------------------------------------------------
George Diamantopoulos
'Not an Access professional.'
'Don't blame me if it doesn't work.'
Quote:
>Hi Adrian. >Try this... it works for me. >Delete any relationships that this table is particpating in.
>Delete the counter field in the table's design view.
>Exit and save the table.
>Re-open the table in design view, put the counter field back in.
>Re-establish any relationships that you deleted above.
>Everyone else suggesting deleting, compacting... I have never tried it but
>will next time.
>I'm only suggesting this cuz I know it works also.
>Don
>> I want to be able to set a counter field value back to 1 - currently it
>has
>> reached 54 but I want to delete
>> all the records and start again at 1!
>> I've tried the tip supplied in the access help file using an append query
>> but this does not achieve want I want!
>> Any ideas
>> Sorry if posted to wrong newgroups!
>> Thanks
|
| Wed, 09 Aug 2000 03:00:00 GMT |
|
|
|
Trevor Be
#12 / 15
|
Help resetting counter field value? On Wed, 18 Feb 1998 17:43:04 -0800 in comp.databases.ms-access,
Quote:
>Assuming this is an autonumber , there is another way to reset the counter
>without compacting.
>delete all records, change the autonumber to a number, save the table,
>change the field back to an autonumber.
I haven't tried in 97, but I do know that Access 2.0 won't allow this. \|||/
/ \
C o o D
-----------------ooO--u--Ooo-------------------------------
To reply my mail, replace the "nospam" in my address with "trevor",
this was put on in defence of the spam robots that roam usenet.
MS Access FAQ now available on my site below.
http://easyweb.easynet.co.uk/~trevor/
Apathy Error: Don't bother striking any key.
|
| Mon, 14 Aug 2000 03:00:00 GMT |
|
|
|
Trevor Be
#13 / 15
|
Help resetting counter field value? On 19 Feb 1998 05:14:05 GMT in comp.databases.ms-access, "Don Leverton"
Quote:
>Hi Adrian. >Try this... it works for me. >Delete any relationships that this table is particpating in.
>Delete the counter field in the table's design view.
>Exit and save the table.
>Re-open the table in design view, put the counter field back in.
>Re-establish any relationships that you deleted above.
>Everyone else suggesting deleting, compacting... I have never tried it but
>will next time.
>I'm only suggesting this cuz I know it works also.
>Don
Dangerous Don, Say your table was suppliers and those suppliers have orders, natually there's gaps in the counter values. After your method there's no longer gaps, and your suppliers now have different IDs. You've just changed a lot of suppliers on a lot of orders and your data is now meaningless. \|||/
/ \
C o o D
-----------------ooO--u--Ooo-------------------------------
To reply my mail, replace the "nospam" in my address with "trevor",
this was put on in defence of the spam robots that roam usenet.
MS Access FAQ now available on my site below.
http://easyweb.easynet.co.uk/~trevor/
Apathy Error: Don't bother striking any key.
|
| Mon, 14 Aug 2000 03:00:00 GMT |
|
|
|
Trevor Be
#14 / 15
|
Help resetting counter field value? On 18 Feb 1998 16:20:16 GMT in comp.databases.ms-access, "Adrian
Quote:
>I want to be able to set a counter field value back to 1 - currently it has >reached 54 but I want to delete >all the records and start again at 1! >I've tried the tip supplied in the access help file using an append query
>but this does not achieve want I want!
>Any ideas
>Sorry if posted to wrong newgroups!
>Thanks
I've seen a lot of frightening solutions in this thread. Adrian, I sincerely hope you followed the first 3 (on my news server at least) from Andrew, Roger & Alden (i.e. Compact the db after deleting unwanted records). This is only safe method, other methods bandied around this thread are a threat to your referential integrity and should only be considered after shooting both of your parents. \|||/
/ \
C o o D
-----------------ooO--u--Ooo-------------------------------
To reply my mail, replace the "nospam" in my address with "trevor",
this was put on in defence of the spam robots that roam usenet.
MS Access FAQ now available on my site below.
http://easyweb.easynet.co.uk/~trevor/
Apathy Error: Don't bother striking any key.
|
| Mon, 14 Aug 2000 03:00:00 GMT |
|
|
|
Arvin Meye
#15 / 15
|
Help resetting counter field value? Quote:
>This is only safe method, other methods bandied around this
>thread are a threat to your referential integrity and should only be
>considered after shooting both of your parents.
From the suggestions I've read, shooting both your parents may be less drastic. You can reorder and renumber a counterfield, but DON'T DO IT. On new databases (all versions of Access) where you have maintenance (validation or lookup) tables, and want a contiguous set of numbers to deliver the database; just delete the counter field, and add it back in after ordering your data. As Trevor mentioned, this is dangerous. If you have related records, kiss
them goodbye. I only do this for things like state lookup tables or postal
code sets, where the data is unlikely to change in the future, and ONLY on
new, empty databases. (Empty meaning devoid of client data)
-----
Arvin Meyer
|
| Wed, 16 Aug 2000 03:00:00 GMT |
|
| |
