Relational Delete Query

[NeonSky via AccessMonster.com]

Hello Everyone!

Here is the scenerio....I have table "A" and table "B" , they share the same
structure.

If field 'f1" in table "A" is not null then delete like record from table "B".
(Table "A" and "B" share a one to one relationship based on "id").

Just to explain in greater detail, I have daily import table and a holding
table. The holding table is a list of records appended daily as a result of a
given field being null. If tommorows import to the import table includes a
like record to the holding table, where the null field is now populated, I
would like the history table to delete this record.

Happy to elaborate further or provide additonal info.

Here is my SQL.

DELETE DISTINCTROW tblVOAHolding.*, tblMainImport.[VOA EID]
FROM tblMainImport INNER JOIN tblVOAHolding ON tblMainImport.[Owner Number] =
tblVOAHolding.[Owner Number]
WHERE (((tblMainImport.[VOA EID]) Is Not Null));

THANKS!!!

 

[Marshall Barton]

Here is the scenerio....I have table "A" and table "B" , they share the same
structure.

If field 'f1" in table "A" is not null then delete like record from table "B".
(Table "A" and "B" share a one to one relationship based on "id").

Just to explain in greater detail, I have daily import table and a holding
table. The holding table is a list of records appended daily as a result of a
given field being null. If tommorows import to the import table includes a
like record to the holding table, where the null field is now populated, I
would like the history table to delete this record.

Here is my SQL.

DELETE DISTINCTROW tblVOAHolding.*, tblMainImport.[VOA EID]
FROM tblMainImport INNER JOIN tblVOAHolding ON tblMainImport.[Owner Number] =
tblVOAHolding.[Owner Number]
WHERE (((tblMainImport.[VOA EID]) Is Not Null));

Close, but you are trying to delete records from both
tables. Assuming I correlated table "A", holding and
history xorrextly, I think this should be better:

DELETE tblVOAHolding.*
FROM tblMainImport INNER JOIN tblVOAHolding ON
tblMainImport.[Owner Number]=tblVOAHolding.[Owner Number]
WHERE tblMainImport.[VOA EID] Is Not Null

 

[NeonSky via AccessMonster.com]

Got it working, Thanks!!

Here is the scenerio....I have table "A" and table "B" , they share the same
structure.

[quoted text clipped - 14 lines]

tblVOAHolding.[Owner Number]
WHERE (((tblMainImport.[VOA EID]) Is Not Null));

Close, but you are trying to delete records from both
tables. Assuming I correlated table "A", holding and
history xorrextly, I think this should be better:

DELETE tblVOAHolding.*
FROM tblMainImport INNER JOIN tblVOAHolding ON
tblMainImport.[Owner Number]=tblVOAHolding.[Owner Number]
WHERE tblMainImport.[VOA EID] Is Not Null