filter a table based on a query

[justine]

What I really want to do is update a table when another table has been
edited.
I have a table of attendence, and another for registration that the
table attendence is created from based on an Append Query.
I managed to create a delete query for when course are cancelled to
delete records in the attendence table, but when an individual cancels
a course, I'm having trouble figuring out how to delete because this
query and table are not linked directly.
So I was thinking alternatively of writing a macro and applying a
query to filter my table and then working out that all 'present'
fields should be unmarked, or having the user do it, but I can't get
it to work...

My attempt at delete query is:

DELETE tblCourse_Attendence2.*, tblRegistration.Cancellation
FROM tblRegistration INNER JOIN tblCourse_Attendence2 ON
(tblRegistration.FullName = tblCourse_Attendence2.Full_Name) AND
(tblRegistration.CourseYr = tblCourse_Attendence2.CourseID)
WHERE (((tblRegistration.Cancellation)=True));

and error message is:

"could not delete from specified tables"

Thanks!
-justine

 

[Michel Walsh]

Try

DELETE DISTINCTROW ....


To delete, over a join, Jet requires the DISTINCTROW keyword (to not
short-circuit the bookkeeping, my guess).



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Why you don't use a cascade delete? if possible, that would be definitively
safer than being obliged to run an extra query... I admit this is not always
doable, though.


Vanderghast, Access MVP

 

[justine]

Good question - because I'm scared!
I've been warned not to use them, although I have cascade update set
up for my relationships, because that helps with data integrity and is
less scary.
The users are not very sophisticated and I fear they may accidentally
delete the wrong type of record, like a form record instead of a
subform record, and then it would trigger some huge chain of deleting
if I did cascade delete...
also, if I do brave that avenue, how, other than my going thru by
hand, do I take out the records that need removing?
Thanks for your thoughts!
-justine

 

[Michel Walsh]

You define an index covering the two fields:

Create a new index, from the index 'sheet' (not from the field
properties section),
which imply to give an index name in the first column, and supply a
first field in the second column.
Next line, skip the first column, and in the second column, supply the
second field. Have this index be 'unique', not allowing duplicated pair.

That will create an index made of the pair of the fields.

Next, you also make a relationship based on these two fields, between the
two tables. If you enforce the cascade deletion, then, any time you remove
the master record of a given value, then all the children records will be
deleted, as your delete query was doing. On the other hand, you can delete
individually one, or many records, in the children table, without any
incident for the parent table.



Hoping it may help,
Vanderghast, Access MVP