Update records

[Jeremy Dove]

I posted a few days ago about how to have a primary key
with say 3 letters and then numbers that autonumber. I
have 2 more questions about it.


1. How can I set the autonumber field to a custom start
point such as 00001?

2. I already have a table with 267 records in it. I have
set the field to pick up the current date with every new
record. How do I update the old records so that I can use
them as a primary key? Do I just need to manually update
them or is there another way?

Thanks

 

[Tim Ferguson]

1. How can I set the autonumber field to a custom start
point such as 00001?

I do not remember the original post, but if it's saying what I think it's
saying, then you should not be using an autonumber. Whatever method you are
planning to assign values should take account of the restart value.

2. I already have a table with 267 records in it. I have
set the field to pick up the current date with every new
record.

Hmm: a small problem. Although having a YearCreated is theoretically
dependent on DateCreated (and therefore outside 3NF), you can't use the
value embedded in the date field to make up a Primary Key, so you will have
to have a separate Integer field. I suppose strict 3NF would then mandate
separate Day and Month fields instead of the date, but with horrific
validation problems. Sometimes you just have to go with the lesser of two
evils.

Anyway, the update is easy as pie:

UPDATE MyTable
SET YearCreated = Year(DateCreated);

Hope that helps


Tim F