Deleting a record and all associated information

[ExponentiaI]

Good evening, I hope someone can help?

I am currently building an access database to manage a hobby group/club that I run.

I've manage to build all of the queries and macros to get me up to the operational point, however one of the issues I have is if an attendee decides to leave.

The group consists of people under 16 and therefore they are linked to a guardian field for payment of subs, etc. The challenge I have is wanting to be able to potentially remove the attendee, and deduct it from the monies paid by the guardian - to give a new total balance (as they may have more then one participant that they look after).
I've used the built in "button" tool to remove the individual record, but then this shows the guardian as being significantly in credit - when in reality the money needs to be removed.

I hope this makes sense, and someone out there can help me with this final milestone?



Thank you

[CJ_London]

difficult to answer without knowing your db design - but look at your table relationships to a) enforce referential integrity and b) cascade delete related records.

Also, by way of note, it is generally advised not to delete data, but to have a flag to indicate the record should be ignored which can then be used as criteria in queries to ignore them. There are a number of benefits - you can still 'undelete' it if it was flagged in error and you maintain a proper history - perhaps the person concerned has been with you for three years, deleting them would affect the view of the previous years.

[ExponentiaI]

difficult to answer without knowing your db design - but look at your table relationships to a) enforce referential integrity and b) cascade delete related records.

Also, by way of note, it is generally advised not to delete data, but to have a flag to indicate the record should be ignored which can then be used as criteria in queries to ignore them. There are a number of benefits - you can still 'undelete' it if it was flagged in error and you maintain a proper history - perhaps the person concerned has been with you for three years, deleting them would affect the view of the previous years.

Hi Ajax

Thank you for replying, it's frustrating I'm so close. I like the idea of being able to hide them rather than delete them - for all of the benefits you have mentioned. I hadn't thought about flagging them as active/inactive.

Thank you

[CJ_London]

if you are going this route, it doesn't have to be a check box - could be a leave date for example and/or a text box to show why left - reached max age, moved away, got bored etc