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

[water]

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

 

[Joan Wild]

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?

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!