changed stations IDs but want to keep old station Ids in DB

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

water - 14 Jan 2005 23:09 GMT

I have put together a water quality database. I have one table that shows
alls the characteristics of my water quality stations (name, location,
description, lat, long etc...). For example one station name (or ID) was PR-1.
Another table that is linked to the station table through the station ID,
holds all the actual water quality data collected from the lab with a date
stamp, time stamp, parameter, water quality station ID, value and flag.

Recently, we have reviewed our nomenclature for naming water quality
stations and the stations were renamed, so PR-1 might be HSC_U now. I want to
add the new IDs in the database but I also want to preserve the old ones. I
want to be able to call the entire recordset for a given station whether I
use the old name or the new name. What is the best way to do this?

Thanks

Esther

Reply to this Message

Joan Wild - 14 Jan 2005 23:26 GMT

> I have put together a water quality database. I have one table that
> shows alls the characteristics of my water quality stations (name,
[quoted text clipped - 10 lines]
> given station whether I use the old name or the new name. What is the
> best way to do this?

I would create a new field in your table called OrigName. Run an update
query to update this field with the Name field (hope you didn't call it
Name).

If you have Cascade Update checked for the relationship between Station Id
to the foreign key in 'other table' then you can run an update query to
update PR-1 to HSC_U.

You would of course, backup before doing any updates!

Signature

Joan Wild
Microsoft Access MVP

Reply to this Message