Delete entries from a One table in a one-to-many rel? 2

[WarriorPrincess]

I have 2 tables in a one-to-many relationship. The "one" table (tblSupplier) contains a SupplierCode field which is unique and is also the primary key. The "many" table (SupplierList) contains data from an imported file which I am going to use to append records to tblSupplier. Field1 contains multiple entries of Supplier codes.

Problem: How do I delete all entries in tblSupplier which have a matching code in SupplierList? The only reasonable way I have come across so far is to add an extra Delete? field in tblSupplier. Do an update query and set the field to True where the fileds match and then do a delete query on all records which have Delete? set to True. This seems somewhat cumbersome and I was hoping that there might be a neater way.

Does anyone have any ideas? TIA

 

[mwolf00]

I'm a little confused as to why you would do this so let me make sure I have this straight...
This code deletes all instances from your primary table where the primary key / foriegn key relationship is actually used??????

DELETE FROM tblSupplier WHERE supplierCode IN (SELECT supplierCode FROM supplierList) -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[WarriorPrincess]

Basically the problem is as follows:
tblSupplier is a &quot;permanent&quot; table in my database which contains information about all our suppliers and is keyed on SupplierCode. Our Order system will be exporting a file each month which will list all details about all our suppliers. Some of the supplier details may have changed on the Order system (but NEVER the supplier code) and I need these amendments to be made to tblSupplier as well as details of any new suppliers to be added.

I figured that the easiest way would be to:
1) trash out any records in tblSupplier which have corresponding suppliercodes (Field1) in SupplierList
2) Perform a MAX function on the date field in SupplierList to pick up the latest supplier entry.
3) Append the details from point 2 into tblSupplier. This would solve the problem of duplicate or old entries.

Your code works brilliantly! Many Thanks.

 

[RickSpr]

WP,

Does tblSupplier have any relationships to other tables that enforce referential integrity? If so, deleting the suppliers will either delete from the other tables (if you cascade deletes), or will fail (if you don't).

I think you should be able to do this with an update query. First, you need a grouping query to find the latest date in SupplierList:
SELECT SupplierCode, MAX(RecordDate) As LatestDate
FROM SupplierList
GROUP BY SupplierCode
(For reference, call this query SupplierLatest.)

Then you join that with the original SupplierList to get the rest of the fields to be loaded into tblSupplier:
SELECT SupplierList.*
FROM SupplierList INNER JOIN SupplierLatest
ON SupplierList.SupplierCode = SupplierLatest.SupplierCode
AND SupplierList.RecordDate = SupplierLatest.LatestDate
(For references, call this query SupplierUpdates.)
(Note: If you should have two records with the same date for the same supplier, this isn't going to work. Is that a possibility?)

Finally, you update tblSupplier:
UPDATE tblSupplier INNER JOIN SupplierUpdates
ON tblSupplier.SupplierCode = SupplierUpdates.SupplierCode
SET tblSupplier.[Field1] = SupplierUpdates.[Field1],
tblSupplier.[Field2] = SupplierUpdates.[Field2]
etc.

Because this is an UPDATE query, you don't have to worry about relational integrity DELETE rules. And because you're not updating the SupplierCode, you don't have to worry about relational integrity UPDATE rules. Rick Sprague

 

[WarriorPrincess]

RickSpr

Thanks for this but I am new to Access and have taught myself out of a book! I can see how your code would do the updates to records which already exist in tblSupplier but how would it add the new records in as well? Wouldn't I have to do a separate Append assuming that a query of &quot;SELECT FROM SupplierList WHERE supplierCode NOT IN (SELECT supplierCode FROM tblSupplier)&quot; would work?

Doing the DELETE *............. and then the Append would do both in one hit and would also be more efficient wouldn't it?

I don't use Referential Integrity. My database design means that very few tables can use it and NONE can use the Cascade Del/Add options.

 

[RickSpr]

You're exactly right. I missed the fact that there were new suppliers in SuppliersList--I thought you only had updates.

But since you don't have referential integrity worries, the DELETE/Append query would definitely be faster and easier for you.

I, too, learned Access out of books (I prefer self-teaching for everything). Is Access your first exposure to SQL? If so, it seems as if you've gotten more out of your studies than the average person. Your grasp of relational set algebra is excellent. Rick Sprague
[red]To write a program from scratch, first create the universe.[/red] - Paraphrased from Albert Einstein

 

[WarriorPrincess]

Rick,

Thanks for the compliment. Yes - this is my first exposure to SQL and I am suffering lots of headaches as a result. NOTHING I want to do seems to be as easy as it seems!

However, the response from this forum has been quick and very helpful so I shall definitely be coming back.