Solved

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

Posted on 2014-11-05
2
244 Views
Last Modified: 2014-11-08
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
0
Comment
Question by:Eur0star1
2 Comments
 
LVL 22

Accepted Solution

by:
rspahitz earned 500 total points
ID: 40424334
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
 

Author Comment

by:Eur0star1
ID: 40425713
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question