Program Table PK to start over at 1

[Eka1618]

Hello,

I have a table (tblQueue) that consists of a composite PK of the fields
[Q_ID] and [TODAY]. In this table, When a new year begins I would like [Q_ID]
to start over again at 1 (This field is an AutoNumber field). Since I am
using a compisite PK will access allow me to do this? I just do not know how
to get it to start over.

If anyone has any suggestions, please let me know, Thank you!

~Erica~

 

[Klatuu]

You can use the DMax function to return the hightest [Q_ID] for a year. If
no [Q_ID] exists for a year, the function will return Null and you can then
assign a value of 1 to it. The expression below will add 1 to the current
highest number if there are already [Q_ID] records for the year and will
return 1 if it is the first for the year:

= Nz(DMax("[Q_ID]", "tblQueue", "Year([TODAY] = " & Year(Date)), 0) + 1

 

[Tom van Stiphout]

On Mon, 19 May 2008 06:03:01 -0700, Eka1618

This is a FAQ. You should have no problem finding posts that state
that this is impossible and also a very bad idea to attach meaning to
an autonumber value.
If you want an ID field that you can control, use a Long Integer and
supply the value in the Form_BeforeInsert event.
Having a PK over an Autonumber PLUS another field is also very curious
and likely indicates bad design. You may want to get professional help
to get the database design straight, then proceed with implementation
yourself.

-Tom.

 

[Klatuu]

Tom is correct. I did not notice you said it was an auto number field. You
should be using a long integer field. Not only can you not change autonumber
fields that way, autnumber fields will not always be sequential. As soon as
the first character is typed into a new record, the autonumber value is
assigned; however, if the user does not complete the record and save it, the
number is not reused.

 

[Eka1618]

Thank you for the help. I ended up changing my Q_ID field to just a number
field in stead of AutoNumber.

~Erica~

You can use the DMax function to return the hightest [Q_ID] for a year. If
no [Q_ID] exists for a year, the function will return Null and you can then
assign a value of 1 to it. The expression below will add 1 to the current
highest number if there are already [Q_ID] records for the year and will
return 1 if it is the first for the year:

= Nz(DMax("[Q_ID]", "tblQueue", "Year([TODAY] = " & Year(Date)), 0) + 1
--
Dave Hargis, Microsoft Access MVP

Hello,

I have a table (tblQueue) that consists of a composite PK of the fields
[Q_ID] and [TODAY]. In this table, When a new year begins I would like [Q_ID]
to start over again at 1 (This field is an AutoNumber field). Since I am
using a compisite PK will access allow me to do this? I just do not know how
to get it to start over.

If anyone has any suggestions, please let me know, Thank you!

~Erica~

 

[Eka1618]

My clients want to use an auto generated field, this is how they've been
doing their business for many years. If the field generates a sequenced
number, then that is what they want to see and use.

I did read some other post after initially asking this question and did find
that I could not use the AutoNumber field for this purpose. I also do not
think I need 'professional help' as Tom suggests; just a few pointers. I am
trying to tweek the DB to work a more efficiently.

In-any-event, the code you provided worked well. Unfortunately it does not
work in my situation.

When records are entered into tblQueue, they are appended from a query
(which is only appending about half of the total fields for a single record
in tblQueue). I receive Append Violation Errors because the QID does not have
a value when the query runs. Is there a way to write this code in a module of
some sort and use it in the query?


~Erica~

Tom is correct. I did not notice you said it was an auto number field. You
should be using a long integer field. Not only can you not change autonumber
fields that way, autnumber fields will not always be sequential. As soon as
the first character is typed into a new record, the autonumber value is
assigned; however, if the user does not complete the record and save it, the
number is not reused.
--
Dave Hargis, Microsoft Access MVP

Hello,

I have a table (tblQueue) that consists of a composite PK of the fields
[Q_ID] and [TODAY]. In this table, When a new year begins I would like [Q_ID]
to start over again at 1 (This field is an AutoNumber field). Since I am
using a compisite PK will access allow me to do this? I just do not know how
to get it to start over.

If anyone has any suggestions, please let me know, Thank you!

~Erica~

 

[Eka1618]

I suppose I could still use the auto number for this table to identify each
record, but just call it something other than Q_ID. Then I could use Q_ID as
my own sequence field as a NUMBER data type. The code that you provided me
will work then and start back at 1 each year.

I think I am going to go with this idea. Thanks again for the help.

~Erica~

Tom is correct. I did not notice you said it was an auto number field. You
should be using a long integer field. Not only can you not change autonumber
fields that way, autnumber fields will not always be sequential. As soon as
the first character is typed into a new record, the autonumber value is
assigned; however, if the user does not complete the record and save it, the
number is not reused.
--
Dave Hargis, Microsoft Access MVP

Hello,

I have a table (tblQueue) that consists of a composite PK of the fields
[Q_ID] and [TODAY]. In this table, When a new year begins I would like [Q_ID]
to start over again at 1 (This field is an AutoNumber field). Since I am
using a compisite PK will access allow me to do this? I just do not know how
to get it to start over.

If anyone has any suggestions, please let me know, Thank you!

~Erica~