Update records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeremy Dove - 15 Jan 2004 14:51 GMT

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

Reply to this Message

Tim Ferguson - 15 Jan 2004 17:52 GMT

> 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

Reply to this Message