Delete Query based on tables joined through composite key

[Lady_Jane]

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.

[jzwp11]

When you say lookup table do you really mean junction table?

tblWatersheds
-pkWaterShedID primary key, autonubmer

tblRegions
-pkRegionID primary key, autonumber


tblWatershedRegions
-pkWaterRegionID primary key, autonumber
-fkWaterShedID foreign key to tblWatersheds
-fkRegionID foreign key to tblRegions
-yourpercentagefield

I also assume that your update table looks like this:

tblWatershedRegionsUpdate
-pkWaterRegionUpdateID primary key, autonumber
-fkWaterShedID foreign key to tblWatersheds
-fkRegionID foreign key to tblRegions
-percentagefieldwithnewvalue

Additionally, I would probably not recommend using a composite primary key field.

My questions, why delete the records and then append new ones? Won't that mess up any related records that rely on the junction table? Why not just run an update query to update the percentage field to the new value for those records that already exist and then run an append query to add any new records?

Also, do these percentages change over time and do you want to see (record) how they change over time?


BTW, the update query would look something like this:

UPDATE tblWatershedRegions INNER JOIN tblWatershedRegionsUpdate ON (tblWatershedRegions.fkRegionID = tblWatershedRegionsUpdate.fkRegionID) AND (tblWatershedRegions.fkWaterShedID = tblWatershedRegionsUpdate.fkWaterShedID) SET tblWatershedRegions.mypercentage = tblWatershedRegionsUpdate.mynewpercentage;

[Lady_Jane]

Hi, thanks for your reply.
I actually found the answer to my question in another forum.
http://www.utteraccess.com/forum/tab...-t1662200.html
That worked great.
The reason why I couldn't do an update (or at least why I thought I couldn't) is that is because not all the necessary combination of primary keys were present in the table already. For example:
The original table has:
watershed ID 2.......Region ID 5.......25%
watershed ID 2.......Region ID 7.......75%
but the new table has:
watershed ID 2.......Region ID 5.......25%
watershed ID 2.......Region ID 7.......25%
watershed ID 2.......Region ID 6.......50%
I needed a new record with primary key watershed ID 2 Region ID 6, which didn't already exist.

No, I don't need to keep a record of the old values, and it's not going to be an ongoing change.

As for "lookup" table, yes I do mean junction, that's just the terminology I was taught.
Thank you for your help.

[jzwp11]

I'm glad you were able to come up with a solution. Good luck as you continue on your project.