Complex Access Query 2

[hirenJ]

Hi all -- I have an updated table of data that I am trying to use to update a Master table. Both tables are in a common key therefore making the relationship is no problem.

How can I write a query that will update all the Master table records with the new data from the update Table?

Similarly, I have a table of records that contain keys of records to be deleted from the Master table. How can I write a query that will delete master table records for this specific list of keys.

Can these lookup queries be done in a simple access query -- or do they have to be done with a bit of code?

thanks

Hj

)



make peace with yourself before you kick the computer...

 

[rac2]

"Similarly, I have a table of records that contain keys of records to be deleted from the Master table. How can I write a query that will delete master table records for this specific list of keys."

DELETE FROM masterTable
WHERE keyColumnID IN
( SELECT keyColumnID FROM toBeDeletedTable)

 

[tlbroadbent]

Update query: Use an INNER JOIN query

Update tblMaster As m
Inner Join tblUpdates u
On m.KeyCol=u.KeyCol
Set m.col1=u.col1, m.col2=u.col2, ..., m.colN=u.colN

You can also update and append at the same time using a Right Join. Any matching records in tblMaster will be updated with data from tblUpdated. Any new records in tblUpdated will be appended to tblMaster.

Update tblMaster As m
Right Join tblUpdates u
On m.KeyCol=u.KeyCol
Set m.col1=u.col1, m.col2=u.col2, ..., m.colN=u.colN

Delete rows from tblMaster that match rows in tblDelete using a LEFT JOIN query.

Delete tblMaster.*
From tblMaster LEFT JOIN tblDelete
ON tblMaster.KeyCol=tblDelete.KeyCol
Where tblDelete.KeyCol Is Null Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[Quehay]

Terry,

This is very cool--I've never seen this before! (update/append where not found in subordinate table):


Update tblMaster As m
Right Join tblUpdates u
On m.KeyCol=u.KeyCol
Set m.col1=u.col1, m.col2=u.col2, ..., m.colN=u.colN

However, maybe I'm not reading well, but wouldn't this delete the unmatched records rather than those found in the "delete" table?

Delete rows from tblMaster that match rows in tblDelete using a LEFT JOIN query.


Delete tblMaster.*
From tblMaster LEFT JOIN tblDelete
ON tblMaster.KeyCol=tblDelete.KeyCol
Where tblDelete.KeyCol Is Null

 

[tlbroadbent]

Quehay,

Thanks for the correction! I'm glad people clean up after me.

HJ,

The query to delete matched records should use an INNER JOIN.

Delete tblMaster.*
From tblMaster INNER JOIN tblDelete
ON tblMaster.KeyCol=tblDelete.KeyCol Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[hirenJ]

hehe,

nope -- it was right the first time (I think) -- I wanted to delete records from the master table -- where the ID's matched those in the delete table...

unfortunately I havent been able to get this code to run yet-- I think its just owner rights issue..at least I hope...

thanks everyone )

Hj make peace with yourself before you kick the computer...