How to remove (not delete !) a link record from a junction table

I have a database with a junction table:

Clients  1>m  Contracts  1>m  link  m<1  Scanners  1>m  reports

Contracts can be added; modified or deleted; this includes adding and removing scanners.
on removing a scanner from a contract I would (I think) need to delete the associated link from the junction table.  However, there are reports for scanners which I must keep.

I've setup a couple of forms to enable me to add contracts and scanners but I do not know how to remove scanners from existing contracts.  Can the link be updated (delete and add) to move the scanner to an "obsolete contract"  -  or is there a better way to handle this removal ?

Thanks
Brian
Eur0star1Asked:
Who is Participating?
 
rspahitzCommented:
If I understand you correctly, you have a many-to-many table called links, which has pointers to the Contracts and Scanners tables.  Each of the records in this table indicates how any Contract can connect to any Scanner.
To remove any one of these, you would normally just remove the record and the connection between the two is gone.

If you want to retain that connect (for audit purposes, for example) you could change the Link table to contain another field called something like "Active" and set the default to True.  Then when you want to disable the connection, set this to False.  However, you will need to update all of your reports to query where Link-table Active = True.
0
 
Eur0star1Author Commented:
That seems a straightforward way of doing  what I need.  I could also add some additional fields to the link and I could add dates to the link which might provide a history.

I thought I would need some DAO to achieve this - which I would be pleased if you could give me a link to a suitable article on this.

Thanks - I have a solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.