Hi everyone
Here is some background on the database if you are interested:
I have I "lookup" table creating a many to many relationship between watersheds and physiographic regions. I have a watersheds table and a physiographic regions table linked together by this lookup table. The lookup table also contains a field "percent" which has the percent of each watershed falling within each region. The lookup table, therefore, has a composite key of Watershed ID and Region ID.
The lookup table needs updating, however, since the percentages have changed. I have another table that contains the same fields with the records that need updating.
I want to delete all the records from the original table that have a corresponding Watershed ID and Region ID in the updated table. Then I want to append all the records from the update table to the existing one.
When I try this:
SELECT lkp_Regions_PSO.*
FROM lkp_Regions_PSO, lkp_Regions_PSO_update
WHERE lkp_Regions_PSO.UID = lkp_Regions_PSO_update.UID AND lkp_Regions_PSO.UNIT = lkp_Regions_PSO_update.UNIT
the selection shows the records to delete, except when I change it to a delete query it says "specify the table containing the records you want to delete".
If I only say FROM lkp_Regions_PSO then it asks for lkp_Regions_PSO_update.UID as a parameter.
Any ideas? By looking up the error message online it seems that it could be solved if I was only using one primary key.