|
Home > Archive > MS Access Multiuser > August 2005 > Obtain new ID number from another table
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Obtain new ID number from another table
|
|
| Mail Merge Help 2005-08-29, 8:26 pm |
| Hi
I have a Customer table with the below fields.
CustID [Autonumber][Primary key]
Name
Address
Sometime I want to archived the non active customer to another
Archived_Customer table where data can't be edit.
The problem is that if I archived the customer with the last CustID to the
Archived_Customer Table, the Customer table will create the new CustID that
could be duplicate to one of the non active customer in the Archived_Customer
table. This will cause Key violations when append to the Archived_Customer
later on.
Is there a way to fix this problem?
I am thinking of obtain the CustID from another table, eg CustID register.
so when ever a new customer ID create it will get the next available number
in the CustID Register table? But I don't know how to do this. Could you
please help.
Thank you very much
Hong
| |
| Steve Schapel 2005-08-30, 1:27 pm |
| Hong,
If CustID is an Autonumber field, then you will not have the problem you
anticipate. If you archive the customer with the highest CustID, the
CustID assigned to the next new customer added will still be the next
higher number again.
--
Steve Schapel, Microsoft Access MVP
Mail Merge Help wrote:
> Hi
>
> I have a Customer table with the below fields.
> CustID [Autonumber][Primary key]
> Name
> Address
>
> Sometime I want to archived the non active customer to another
> Archived_Customer table where data can't be edit.
>
> The problem is that if I archived the customer with the last CustID to the
> Archived_Customer Table, the Customer table will create the new CustID that
> could be duplicate to one of the non active customer in the Archived_Customer
> table. This will cause Key violations when append to the Archived_Customer
> later on.
>
> Is there a way to fix this problem?
> I am thinking of obtain the CustID from another table, eg CustID register.
> so when ever a new customer ID create it will get the next available number
> in the CustID Register table? But I don't know how to do this. Could you
> please help.
>
> Thank you very much
> Hong
| |
| Mail Merge Help 2005-08-30, 8:29 pm |
| Steve.
Say if 10 is the highest number in the Customer table, I archived 10. When I
say I archived mean that I transfer 10 to another table and delete 10 from
the Customer table. therefore number 9 will be the highest. so when a new
record created, 10 will be the next number.
Am I right?
Thanks
"Steve Schapel" wrote:
> Hong,
>
> If CustID is an Autonumber field, then you will not have the problem you
> anticipate. If you archive the customer with the highest CustID, the
> CustID assigned to the next new customer added will still be the next
> higher number again.
>
> --
> Steve Schapel, Microsoft Access MVP
>
>
> Mail Merge Help wrote:
>
| |
| Steve Schapel 2005-08-30, 8:29 pm |
| Hong,
No, you are not right. If CustID is an Autonumber field, then a new
customer added would have CustID 11. An Autonumber field does not
increment based on the highest existing number in the table, it
increments according to the highest number that has *ever existed* in
the table.
--
Steve Schapel, Microsoft Access MVP
Mail Merge Help wrote:
> Steve.
>
> Say if 10 is the highest number in the Customer table, I archived 10. When I
> say I archived mean that I transfer 10 to another table and delete 10 from
> the Customer table. therefore number 9 will be the highest. so when a new
> record created, 10 will be the next number.
>
> Am I right?
>
> Thanks
| |
| Mail Merge Help 2005-08-30, 8:29 pm |
| Steve
Thanks, I just tried it out and yes, the next number is 11 not 10.
Thank you very much for your help.
Hong
"Steve Schapel" wrote:
> Hong,
>
> No, you are not right. If CustID is an Autonumber field, then a new
> customer added would have CustID 11. An Autonumber field does not
> increment based on the highest existing number in the table, it
> increments according to the highest number that has *ever existed* in
> the table.
>
> --
> Steve Schapel, Microsoft Access MVP
>
>
> Mail Merge Help wrote:
>
|
|
|
|
|