Changing the Autonumber value of already created records

D

DawnTreader

Hello

i have a table where an auto number was created as the ID field and the user
typed in the real world number. then at one point we forced the autonumber to
match the real world number so that we didnt have to type in the number
manually.

how can i change the value of the original autonumber records to match the
typed in number so that i can get rid of the "legacy" manually typed in
number?
 
J

John W. Vinson

Hello

i have a table where an auto number was created as the ID field and the user
typed in the real world number. then at one point we forced the autonumber to
match the real world number so that we didnt have to type in the number
manually.

how can i change the value of the original autonumber records to match the
typed in number so that i can get rid of the "legacy" manually typed in
number?
Click to expand...

You can't. Autonumbers are not editable. And they should NOT be used in this
way, or in any way where you're trying to assign meaning to the number; they
will always develop gaps (just hitting <ESC> after you start a new record will
leave a gap, running an Append query can give a huge gap) and they can become
random.

If you want a sequential, human-meaningful ID, use a Long Integer number
field; you can use code on your data entry form to automatically increment it.
 
D

DawnTreader

Hello Ken and John

thanks for the warnings. i have no intention of maintaining a consecutive
number.

the problem is not making it an editable number or human friendly. the
problem is there are 2 columns that are being used by queires and mechanizms
that make things inefficient because i have to allow for both columns to be
the determining factor in the results of those queries and mechanizms.

the idea is that if i could make it all one column that is autonumbered
without upsetting the apple cart i could reduce some of the bog in my code,
queries and the overall performance of the app.

it is just an idea, but something i might figure out how to do so i can get
rid of an extra column in my table and streamline a bunch of code.

the only problem is that everyone is used to the current set of numbers, so
i would like to maintain them as is. that means changing some auto numbers.

here is an example of a query i use that shows how often this situation
causes me grief:

SELECT DISTINCTROW
qryProductSiteInformation.SerialNumber,

IIf(tblWarrantyClaim!WarrantyClaimID<7000,tblWarrantyClaim!ClaimNumber,tblWarrantyClaim!WarrantyClaimID) AS [Claim #],
tblWarrantyClaim.DateofClaim,
tblWarrantyClaim.DateOfFailure,
tblWarrantyClaim.DateOfRepair,
tblWarrantyClaim.WarrantyClaimID,
qryProductSiteInformation.ProductID,
tblServiceReps.Name,
qryProductSiteInformation.ProductTypeID,
tblWarrantyClaim.DateApproved,
tblWarrantyClaim.DateRejected,
tblWarrantyClaim.DateCreditCreated,
tblWarrantyClaim.CreditNumber,
tblWarrantyClaim.WCDateDeleted,
qryAllWarrantyTotals.TotalParts,
qryAllWarrantyTotals.TotalLabour,
qryAllWarrantyTotals.TotalTravel,
qryAllWarrantyTotals.TotalOtherCredits,
IIf([TotalParts] Is Null,0,[TotalParts])+IIf([TotalLabour] Is
Null,0,[TotalLabour])+IIf([TotalTravel] Is
Null,0,[TotalTravel])+IIf([TotalOtherCredits] Is Null,0,[TotalOtherCredits])
AS WarrantyTotal,
subtblWarrantyStatus.Status,
qryAllWarrantyTotals.FirstOfReturnAuthorizationNumber AS RMA
FROM
subtblWarrantyStatus
RIGHT JOIN (tblServiceReps
RIGHT JOIN ((tblWarrantyClaim
LEFT JOIN qryProductSiteInformation
ON tblWarrantyClaim.ProductID = qryProductSiteInformation.ProductID)
LEFT JOIN qryAllWarrantyTotals
ON tblWarrantyClaim.WarrantyClaimID =
qryAllWarrantyTotals.WarrantyClaimID)
ON tblServiceReps.ServiceRepID = tblWarrantyClaim.ServiceRepID)
ON subtblWarrantyStatus.StatusID = tblWarrantyClaim.StatusID
GROUP BY
qryProductSiteInformation.SerialNumber,

IIf(tblWarrantyClaim!WarrantyClaimID<7000,tblWarrantyClaim!ClaimNumber,tblWarrantyClaim!WarrantyClaimID),
tblWarrantyClaim.DateofClaim,
tblWarrantyClaim.DateOfFailure,
tblWarrantyClaim.DateOfRepair,
tblWarrantyClaim.WarrantyClaimID,
qryProductSiteInformation.ProductID,
tblServiceReps.Name,
qryProductSiteInformation.ProductTypeID,
tblWarrantyClaim.DateApproved,
tblWarrantyClaim.DateRejected,
tblWarrantyClaim.DateCreditCreated,
tblWarrantyClaim.CreditNumber,
tblWarrantyClaim.WCDateDeleted,
qryAllWarrantyTotals.TotalParts,
qryAllWarrantyTotals.TotalLabour,
qryAllWarrantyTotals.TotalTravel,
qryAllWarrantyTotals.TotalOtherCredits,
IIf([TotalParts] Is Null,0,[TotalParts])+IIf([TotalLabour] Is
Null,0,[TotalLabour])+IIf([TotalTravel] Is
Null,0,[TotalTravel])+IIf([TotalOtherCredits] Is Null,0,[TotalOtherCredits]),
subtblWarrantyStatus.Status,
qryAllWarrantyTotals.FirstOfReturnAuthorizationNumber,
subtblWarrantyStatus.Order,
tblWarrantyClaim.DateofClaim
HAVING
(((tblWarrantyClaim.DateofClaim) Is Null
Or (tblWarrantyClaim.DateofClaim) Between
[Forms]![frmManageAssets]![txtDateofClaimFromHidden] And
[Forms]![frmManageAssets]![txtDateofClaimToHidden])
AND ((qryProductSiteInformation.ProductID) Like
[forms]![frmManageAssets]![txtProduct])
AND ((tblServiceReps.Name) Like [Forms]![frmManageAssets]![txtRepHidden]
& "*")
AND ((tblWarrantyClaim.CreditNumber) Like
[Forms]![frmManageAssets]![txtCreditNumberHidden] & "*" Or
(tblWarrantyClaim.CreditNumber) Is Null)
AND ((tblWarrantyClaim.WCDateDeleted) Is Null) AND
((subtblWarrantyStatus.Status) Like
[Forms]![frmManageAssets]![txtStatusHidden])
AND
((IIf([tblWarrantyClaim]![WarrantyClaimID]<7000,[tblWarrantyClaim]![ClaimNumber],[tblWarrantyClaim]![WarrantyClaimID]))
Like [Forms]![frmManageAssets]![txtClaimNumberHidden] & "*"
Or
(IIf([tblWarrantyClaim]![WarrantyClaimID]<7000,[tblWarrantyClaim]![ClaimNumber],[tblWarrantyClaim]![WarrantyClaimID])) Is Null))
ORDER BY
subtblWarrantyStatus.Order, tblWarrantyClaim.DateofClaim DESC ,

IIf([tblWarrantyClaim]![WarrantyClaimID]<7000,[tblWarrantyClaim]![ClaimNumber],[tblWarrantyClaim]![WarrantyClaimID]) DESC;

Notice the part that appears 3 times:

IIf(tblWarrantyClaim!WarrantyClaimID<7000,tblWarrantyClaim!ClaimNumber,tblWarrantyClaim!WarrantyClaimID)

it would be so much nicer if i could just go:

tblWarrantyClaim!WarrantyClaimID AS Claim #

dont you think?
 
D

DawnTreader

Hello

i think you are using the autonumber for a sequential sequencing. that wont
work if you delete records. i have 1000's of records using an autonumber and
it has never "crapped" out. its not sequential, but then i never use it to
be. i have no need for a sequential number and if i did i would use a
different method for generating that number.

check ken's first post.
 
D

DawnTreader

Hello Ken

Been a while I know, but I have a new quesiton.

Is there a way to keep the relationships? or am I going to have to reconnect
every thing?

KenSheridan via AccessMonster.com said:
So what you want as far as I can see is a single column which retains the
current WarrantyClaimID value where the value is 7000 or more, and the
ClaimNumber value if the WarrantyClaimID value is less than 7000. For new
rows the number in this column will be arbitrary and automatically inserted.
Right?

I assume that WarrantyClaimID is the autonumber.

The only way I can see of doing this is, as I said in my first post, to make
a copy of the table structure, delete the ClaimNumber column. Then insert
rows from the existing table:

INSERT INTO tblWarrantyClaimNew (WarrantyClaimID,
DateofClaim, DateOfFailure, etc)
SELECT IIf(WarrantyClaimID<7000,ClaimNumber,WarrantyClaimID),
DateofClaim, DateOfFailure, etc
FROM tblWarrantyClaim;

If you are happy with the new table rename the old one to tblWarrantyClaimOld
(I wouldn't delete it completely just yet) and the new one to the original
name, tblWarrantyClaim. This does assume of course that there are no rows
with a WarrantyClaimID of less than 7000 and a ClaimNumber more than 7000
where the ClaimNumber value is matched by a WarrantyClaimID in another row.
Otherwise there would be a violation of the unique index on the
WarrantyClaimID column in the new table.

Ken Sheridan
Stafford, England
Hello Ken and John

thanks for the warnings. i have no intention of maintaining a consecutive
number.

the problem is not making it an editable number or human friendly. the
problem is there are 2 columns that are being used by queires and mechanizms
that make things inefficient because i have to allow for both columns to be
the determining factor in the results of those queries and mechanizms.

the idea is that if i could make it all one column that is autonumbered
without upsetting the apple cart i could reduce some of the bog in my code,
queries and the overall performance of the app.

it is just an idea, but something i might figure out how to do so i can get
rid of an extra column in my table and streamline a bunch of code.

the only problem is that everyone is used to the current set of numbers, so
i would like to maintain them as is. that means changing some auto numbers.

here is an example of a query i use that shows how often this situation
causes me grief:

SELECT DISTINCTROW
qryProductSiteInformation.SerialNumber,

IIf(tblWarrantyClaim!WarrantyClaimID<7000,tblWarrantyClaim!ClaimNumber,tblWarrantyClaim!WarrantyClaimID) AS [Claim #],
tblWarrantyClaim.DateofClaim,
tblWarrantyClaim.DateOfFailure,
tblWarrantyClaim.DateOfRepair,
tblWarrantyClaim.WarrantyClaimID,
qryProductSiteInformation.ProductID,
tblServiceReps.Name,
qryProductSiteInformation.ProductTypeID,
tblWarrantyClaim.DateApproved,
tblWarrantyClaim.DateRejected,
tblWarrantyClaim.DateCreditCreated,
tblWarrantyClaim.CreditNumber,
tblWarrantyClaim.WCDateDeleted,
qryAllWarrantyTotals.TotalParts,
qryAllWarrantyTotals.TotalLabour,
qryAllWarrantyTotals.TotalTravel,
qryAllWarrantyTotals.TotalOtherCredits,
IIf([TotalParts] Is Null,0,[TotalParts])+IIf([TotalLabour] Is
Null,0,[TotalLabour])+IIf([TotalTravel] Is
Null,0,[TotalTravel])+IIf([TotalOtherCredits] Is Null,0,[TotalOtherCredits])
AS WarrantyTotal,
subtblWarrantyStatus.Status,
qryAllWarrantyTotals.FirstOfReturnAuthorizationNumber AS RMA
FROM
subtblWarrantyStatus
RIGHT JOIN (tblServiceReps
RIGHT JOIN ((tblWarrantyClaim
LEFT JOIN qryProductSiteInformation
ON tblWarrantyClaim.ProductID = qryProductSiteInformation.ProductID)
LEFT JOIN qryAllWarrantyTotals
ON tblWarrantyClaim.WarrantyClaimID =
qryAllWarrantyTotals.WarrantyClaimID)
ON tblServiceReps.ServiceRepID = tblWarrantyClaim.ServiceRepID)
ON subtblWarrantyStatus.StatusID = tblWarrantyClaim.StatusID
GROUP BY
qryProductSiteInformation.SerialNumber,

IIf(tblWarrantyClaim!WarrantyClaimID<7000,tblWarrantyClaim!ClaimNumber,tblWarrantyClaim!WarrantyClaimID),
tblWarrantyClaim.DateofClaim,
tblWarrantyClaim.DateOfFailure,
tblWarrantyClaim.DateOfRepair,
tblWarrantyClaim.WarrantyClaimID,
qryProductSiteInformation.ProductID,
tblServiceReps.Name,
qryProductSiteInformation.ProductTypeID,
tblWarrantyClaim.DateApproved,
tblWarrantyClaim.DateRejected,
tblWarrantyClaim.DateCreditCreated,
tblWarrantyClaim.CreditNumber,
tblWarrantyClaim.WCDateDeleted,
qryAllWarrantyTotals.TotalParts,
qryAllWarrantyTotals.TotalLabour,
qryAllWarrantyTotals.TotalTravel,
qryAllWarrantyTotals.TotalOtherCredits,
IIf([TotalParts] Is Null,0,[TotalParts])+IIf([TotalLabour] Is
Null,0,[TotalLabour])+IIf([TotalTravel] Is
Null,0,[TotalTravel])+IIf([TotalOtherCredits] Is Null,0,[TotalOtherCredits]),
subtblWarrantyStatus.Status,
qryAllWarrantyTotals.FirstOfReturnAuthorizationNumber,
subtblWarrantyStatus.Order,
tblWarrantyClaim.DateofClaim
HAVING
(((tblWarrantyClaim.DateofClaim) Is Null
Or (tblWarrantyClaim.DateofClaim) Between
[Forms]![frmManageAssets]![txtDateofClaimFromHidden] And
[Forms]![frmManageAssets]![txtDateofClaimToHidden])
AND ((qryProductSiteInformation.ProductID) Like
[forms]![frmManageAssets]![txtProduct])
AND ((tblServiceReps.Name) Like [Forms]![frmManageAssets]![txtRepHidden]
& "*")
AND ((tblWarrantyClaim.CreditNumber) Like
[Forms]![frmManageAssets]![txtCreditNumberHidden] & "*" Or
(tblWarrantyClaim.CreditNumber) Is Null)
AND ((tblWarrantyClaim.WCDateDeleted) Is Null) AND
((subtblWarrantyStatus.Status) Like
[Forms]![frmManageAssets]![txtStatusHidden])
AND
((IIf([tblWarrantyClaim]![WarrantyClaimID]<7000,[tblWarrantyClaim]![ClaimNumber],[tblWarrantyClaim]![WarrantyClaimID]))
Like [Forms]![frmManageAssets]![txtClaimNumberHidden] & "*"
Or
(IIf([tblWarrantyClaim]![WarrantyClaimID]<7000,[tblWarrantyClaim]![ClaimNumber],[tblWarrantyClaim]![WarrantyClaimID])) Is Null))
ORDER BY
subtblWarrantyStatus.Order, tblWarrantyClaim.DateofClaim DESC ,

IIf([tblWarrantyClaim]![WarrantyClaimID]<7000,[tblWarrantyClaim]![ClaimNumber],[tblWarrantyClaim]![WarrantyClaimID]) DESC;

Notice the part that appears 3 times:

IIf(tblWarrantyClaim!WarrantyClaimID<7000,tblWarrantyClaim!ClaimNumber,tblWarrantyClaim!WarrantyClaimID)

it would be so much nicer if i could just go:

tblWarrantyClaim!WarrantyClaimID AS Claim #

dont you think?
Hello
Click to expand...
[quoted text clipped - 15 lines]
If you want a sequential, human-meaningful ID, use a Long Integer number
field; you can use code on your data entry form to automatically increment it.
Click to expand...
Click to expand...
Click to expand...
 
D

DawnTreader

Hello again

k, i think this is pointless, unless there is some miracle system or code
that i can use. the WarrantyClaimID is used in relationships in other tables.
to change it in the one would mean having to change them in the other.

is there an easy way to do that? the other fields are all just number fields
that store the ID of a particular warranty claim, its all 1 warrantyclaimid
to many in the other tables.

my guess is that i would need a massive update query or some code to go
through each table and change the value.

is this possible?

DawnTreader said:
Hello Ken

Been a while I know, but I have a new quesiton.

Is there a way to keep the relationships? or am I going to have to reconnect
every thing?

KenSheridan via AccessMonster.com said:
So what you want as far as I can see is a single column which retains the
current WarrantyClaimID value where the value is 7000 or more, and the
ClaimNumber value if the WarrantyClaimID value is less than 7000. For new
rows the number in this column will be arbitrary and automatically inserted.
Right?

I assume that WarrantyClaimID is the autonumber.

The only way I can see of doing this is, as I said in my first post, to make
a copy of the table structure, delete the ClaimNumber column. Then insert
rows from the existing table:

INSERT INTO tblWarrantyClaimNew (WarrantyClaimID,
DateofClaim, DateOfFailure, etc)
SELECT IIf(WarrantyClaimID<7000,ClaimNumber,WarrantyClaimID),
DateofClaim, DateOfFailure, etc
FROM tblWarrantyClaim;

If you are happy with the new table rename the old one to tblWarrantyClaimOld
(I wouldn't delete it completely just yet) and the new one to the original
name, tblWarrantyClaim. This does assume of course that there are no rows
with a WarrantyClaimID of less than 7000 and a ClaimNumber more than 7000
where the ClaimNumber value is matched by a WarrantyClaimID in another row.
Otherwise there would be a violation of the unique index on the
WarrantyClaimID column in the new table.

Ken Sheridan
Stafford, England
Hello Ken and John

thanks for the warnings. i have no intention of maintaining a consecutive
number.

the problem is not making it an editable number or human friendly. the
problem is there are 2 columns that are being used by queires and mechanizms
that make things inefficient because i have to allow for both columns to be
the determining factor in the results of those queries and mechanizms.

the idea is that if i could make it all one column that is autonumbered
without upsetting the apple cart i could reduce some of the bog in my code,
queries and the overall performance of the app.

it is just an idea, but something i might figure out how to do so i can get
rid of an extra column in my table and streamline a bunch of code.

the only problem is that everyone is used to the current set of numbers, so
i would like to maintain them as is. that means changing some auto numbers.

here is an example of a query i use that shows how often this situation
causes me grief:

SELECT DISTINCTROW
qryProductSiteInformation.SerialNumber,

IIf(tblWarrantyClaim!WarrantyClaimID<7000,tblWarrantyClaim!ClaimNumber,tblWarrantyClaim!WarrantyClaimID) AS [Claim #],
tblWarrantyClaim.DateofClaim,
tblWarrantyClaim.DateOfFailure,
tblWarrantyClaim.DateOfRepair,
tblWarrantyClaim.WarrantyClaimID,
qryProductSiteInformation.ProductID,
tblServiceReps.Name,
qryProductSiteInformation.ProductTypeID,
tblWarrantyClaim.DateApproved,
tblWarrantyClaim.DateRejected,
tblWarrantyClaim.DateCreditCreated,
tblWarrantyClaim.CreditNumber,
tblWarrantyClaim.WCDateDeleted,
qryAllWarrantyTotals.TotalParts,
qryAllWarrantyTotals.TotalLabour,
qryAllWarrantyTotals.TotalTravel,
qryAllWarrantyTotals.TotalOtherCredits,
IIf([TotalParts] Is Null,0,[TotalParts])+IIf([TotalLabour] Is
Null,0,[TotalLabour])+IIf([TotalTravel] Is
Null,0,[TotalTravel])+IIf([TotalOtherCredits] Is Null,0,[TotalOtherCredits])
AS WarrantyTotal,
subtblWarrantyStatus.Status,
qryAllWarrantyTotals.FirstOfReturnAuthorizationNumber AS RMA
FROM
subtblWarrantyStatus
RIGHT JOIN (tblServiceReps
RIGHT JOIN ((tblWarrantyClaim
LEFT JOIN qryProductSiteInformation
ON tblWarrantyClaim.ProductID = qryProductSiteInformation.ProductID)
LEFT JOIN qryAllWarrantyTotals
ON tblWarrantyClaim.WarrantyClaimID =
qryAllWarrantyTotals.WarrantyClaimID)
ON tblServiceReps.ServiceRepID = tblWarrantyClaim.ServiceRepID)
ON subtblWarrantyStatus.StatusID = tblWarrantyClaim.StatusID
GROUP BY
qryProductSiteInformation.SerialNumber,

IIf(tblWarrantyClaim!WarrantyClaimID<7000,tblWarrantyClaim!ClaimNumber,tblWarrantyClaim!WarrantyClaimID),
tblWarrantyClaim.DateofClaim,
tblWarrantyClaim.DateOfFailure,
tblWarrantyClaim.DateOfRepair,
tblWarrantyClaim.WarrantyClaimID,
qryProductSiteInformation.ProductID,
tblServiceReps.Name,
qryProductSiteInformation.ProductTypeID,
tblWarrantyClaim.DateApproved,
tblWarrantyClaim.DateRejected,
tblWarrantyClaim.DateCreditCreated,
tblWarrantyClaim.CreditNumber,
tblWarrantyClaim.WCDateDeleted,
qryAllWarrantyTotals.TotalParts,
qryAllWarrantyTotals.TotalLabour,
qryAllWarrantyTotals.TotalTravel,
qryAllWarrantyTotals.TotalOtherCredits,
IIf([TotalParts] Is Null,0,[TotalParts])+IIf([TotalLabour] Is
Null,0,[TotalLabour])+IIf([TotalTravel] Is
Null,0,[TotalTravel])+IIf([TotalOtherCredits] Is Null,0,[TotalOtherCredits]),
subtblWarrantyStatus.Status,
qryAllWarrantyTotals.FirstOfReturnAuthorizationNumber,
subtblWarrantyStatus.Order,
tblWarrantyClaim.DateofClaim
HAVING
(((tblWarrantyClaim.DateofClaim) Is Null
Or (tblWarrantyClaim.DateofClaim) Between
[Forms]![frmManageAssets]![txtDateofClaimFromHidden] And
[Forms]![frmManageAssets]![txtDateofClaimToHidden])
AND ((qryProductSiteInformation.ProductID) Like
[forms]![frmManageAssets]![txtProduct])
AND ((tblServiceReps.Name) Like [Forms]![frmManageAssets]![txtRepHidden]
& "*")
AND ((tblWarrantyClaim.CreditNumber) Like
[Forms]![frmManageAssets]![txtCreditNumberHidden] & "*" Or
(tblWarrantyClaim.CreditNumber) Is Null)
AND ((tblWarrantyClaim.WCDateDeleted) Is Null) AND
((subtblWarrantyStatus.Status) Like
[Forms]![frmManageAssets]![txtStatusHidden])
AND
((IIf([tblWarrantyClaim]![WarrantyClaimID]<7000,[tblWarrantyClaim]![ClaimNumber],[tblWarrantyClaim]![WarrantyClaimID]))
Like [Forms]![frmManageAssets]![txtClaimNumberHidden] & "*"
Or
(IIf([tblWarrantyClaim]![WarrantyClaimID]<7000,[tblWarrantyClaim]![ClaimNumber],[tblWarrantyClaim]![WarrantyClaimID])) Is Null))
ORDER BY
subtblWarrantyStatus.Order, tblWarrantyClaim.DateofClaim DESC ,

IIf([tblWarrantyClaim]![WarrantyClaimID]<7000,[tblWarrantyClaim]![ClaimNumber],[tblWarrantyClaim]![WarrantyClaimID]) DESC;

Notice the part that appears 3 times:

IIf(tblWarrantyClaim!WarrantyClaimID<7000,tblWarrantyClaim!ClaimNumber,tblWarrantyClaim!WarrantyClaimID)

it would be so much nicer if i could just go:

tblWarrantyClaim!WarrantyClaimID AS Claim #

dont you think?

Hello

[quoted text clipped - 15 lines]
If you want a sequential, human-meaningful ID, use a Long Integer number
field; you can use code on your data entry form to automatically increment it.
Click to expand...
Click to expand...
Click to expand...
 
D

DawnTreader

Hello Again

first thanks for all the help and suggestions.

this should be the last question about this.

in your experience is it worth it to go through this trouble? the queries
work, everything works pretty much the way we need it to, and we are moving
to a bigger better system in the future, when no one knows, but for the time
being should i just live with this problem or should i make the effort to fix
it?

will it make it more efficient? will it noticably speed up the app? is there
any compelling reason to do this other than "i can"?

personally it bugs me, but we have gotten by with it like this for almost 2
years, and in 6 to 8 months we might begin migration to the new system. i
dont know if i should be doing this because it needs to be done or because i
want to... i need to know if there is justification to spend my work hours
fixing it, not to mention the other people who wont be able to use the app
while i fix it.

thanks :)

KenSheridan via AccessMonster.com said:
I hope the following addresses what you have in mind.

If you assign a new set of values as the primary key of a table, then in any
tables which reference it the values of the foreign key would need to be
updated so they point to the correct rows in the referenced table. The
easiest way of doing this is to temporarily include a column in the table
with the old primary key values. So if you add another column,
WarrantyClaimIDOld, to the tblWarrantyClaimNew table the SQL to fill the new
table would be:

INSERT INTO tblWarrantyClaimNew (WarrantyClaimIDOld,
WarrantyClaimID, DateofClaim, DateOfFailure, etc)
SELECT WarrantyClaimIDOld, IIf(WarrantyClaimID<7000,ClaimNumber,
WarrantyClaimID),
DateofClaim, DateOfFailure, etc
FROM tblWarrantyClaim;

Then delete any relationships between the original tblWarrantyClaim table and
other referencing tables.

In an update query you can then join the tblWarrantyClaimNew table to any
related table which referenced the WarrantyClaimID of the original
tblWarrantyClaim table on the WarrantyClaimIDOld column in the new table, and
update the foreign key column in the referencing table to the value of the
WarrantyClaimID column in the tblWarrantyClaimNew table. You should then be
able to create an enforced relationship between the tblWarrantyClaimNew table
and the referencing table on the WarrantyClaimID columns.

Once you are happy that everything is working correctly you can delete the
WarrantyClaimIDOld column from the new table, delete the original table and
rename tblWarrantyClaimNew to the name of the original tblWarrantyClaim table.


Its absolutely imperative before doing this that you BACK UP the database
securely of course.

Ken Sheridan
Stafford, England
Hello Ken

Been a while I know, but I have a new quesiton.

Is there a way to keep the relationships? or am I going to have to reconnect
every thing?
So what you want as far as I can see is a single column which retains the
current WarrantyClaimID value where the value is 7000 or more, and the
Click to expand...
[quoted text clipped - 152 lines]
If you want a sequential, human-meaningful ID, use a Long Integer number
field; you can use code on your data entry form to automatically increment it.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copying Mainform and Subform data onto new Mainform/Subform 3
Autonumber 7
Auto number in an existing table 4
Autonumber suddenly reverts to earlier numbers 2
Replace deleted records in table that uses Autonumber 6
REPOST - I need to make the IDNumber an Autonumber - HOW?? 1
How can I Manually Change Access Primary Key ID Fields ? 2
Create custom autonumber field 5

Top