Implementing Partial Participation

[Benjix1991]

Hi all,



Apologies if the question has been asked and answered previously but I've not been able to find them.

I have designed a database for work. I have a table detailing funding available that requires all details of the user/project, etc.

In most cases, this can be progressed by the user but in a few cases requires sign off from a manager.

Therefore, the [Manager Approval] table and the [Funding] table do have a relationship, however, the relationship is a partial participation in that the Manager Approval is not required for ALL funding requests to be progressed.

My question is: How do I represent this in Access? Additionally, for those tuples linked through a relationship, my desire is that the deletion of a Funding award would result in the deletion of the Manager Approval details but I have experienced difficulty establishing this and as a result, extraneous data is retained.

Any help would be appreciated.

Many Thanks,
Ben

[June7]

Perhaps you need a field in funding table that flags record as "manager approval required". A yes/no field could probably serve then code would validate the data. The real trick is figuring out what event to put code in.

Deleting records should be a rare event. Why would you delete funding record? Why not just have a field that changes its status - Approved, Canceled, etc.

Are you setting relationships in Relationship builder? Did you set cascade delete?

[orange]

Further to June's advice, can you show readers your tables and relationships? A jpg of your relationships window would help.
Generally in a funding/finance application all transactions (adds/edits/deletes) are simply flagged and not physically deleted.
Is the requirement for Manager approval based on transaction type, transaction amount/threshold, specific fund, or specific employee...??? A little more info about your processes and logic would help with context.
Good luck with your project.

[Benjix1991]

Hi there,

I take your point that the data would not usually be deleted. GDPR comes into play next month and resultantly, our organisation is twitchy about storing data.

Some information retained is sensitive and therefore when not required, should be able to be deleted.

I had hoped to achieve this via a cascade delete but it's hard to see how to set up any relationship that isn't at a minimum 1..1. Anyway, I'll just write a delete query that can executed when a user deletes a funding record.

Thanks for your help!
Ben

[June7]

Why would cascade delete not work? Delete the main record and if there are any dependent records they will delete as well. If there aren't any dependent records the main record will still delete.