Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
How / where can I add records to Junction Table in MS Access ?
Am using 2010/13 Access, 32-bit and the basic database structure is:
Clients 1>m Contracts 1>m Link m<1 Scanners 1>m Reports
I'm able to create and add Contract Records and Scanner Records to the appropriate Tables but I cannot work out how to add the link records to the Link table. Typically a contract has 1 to 3 scanners and I would be grateful if you could tell me which event(s) to use and suitable code (DAO ?) to use to add the links. The Link table holds the ScannerID and ContractID and am intending adding an insert date to the link. Some direction on deleting these links would also be useful.
I have searched but have found the best article s are SQL based but a direction to suitable (for beginners !) articles would be appreciated.
Thanks
Brian
Clients 1>m Contracts 1>m Link m<1 Scanners 1>m Reports
I'm able to create and add Contract Records and Scanner Records to the appropriate Tables but I cannot work out how to add the link records to the Link table. Typically a contract has 1 to 3 scanners and I would be grateful if you could tell me which event(s) to use and suitable code (DAO ?) to use to add the links. The Link table holds the ScannerID and ContractID and am intending adding an insert date to the link. Some direction on deleting these links would also be useful.
I have searched but have found the best article s are SQL based but a direction to suitable (for beginners !) articles would be appreciated.
Thanks
Brian
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Are you using form for inserting data? Normally this table could be subform in form with Contracts. Look at sample
DBContracts.accdb
DBContracts.accdb
You can avoid the complexities of many to many relationships by using multi value fields ....
IF the list of values is not long
http://www.techrepublic.com/blog/microsoft-office/how-to-use-the-new-multivalue-field-in-access-2007-2010/
http://www.webucator.com/blog/2010/06/creating-a-multi-value-field-in-access-2010/
==========================
You don't explicitly enter data into a link table.
Once you enter data into your forms, entries are autmatically entered into "The behind the scene" join table.
There is a huge amount of data out there on "Many To Many" relationships ... a few examples
http://office.microsoft.com/en-gb/access-help/create-relationships-for-a-new-database-RZ101772998.aspx?section=9
http://www.health.ufl.edu/~prcowart/
Have a look at the "Examples" folder and download some sample databases (ans a useful doc file)
You can't beat a real working example. I have a database (somewhere) with a many-many relationship.
I'll post it on Monday.
IF the list of values is not long
http://www.techrepublic.com/blog/microsoft-office/how-to-use-the-new-multivalue-field-in-access-2007-2010/
http://www.webucator.com/blog/2010/06/creating-a-multi-value-field-in-access-2010/
==========================
You don't explicitly enter data into a link table.
Once you enter data into your forms, entries are autmatically entered into "The behind the scene" join table.
There is a huge amount of data out there on "Many To Many" relationships ... a few examples
http://office.microsoft.com/en-gb/access-help/create-relationships-for-a-new-database-RZ101772998.aspx?section=9
http://www.health.ufl.edu/~prcowart/
Have a look at the "Examples" folder and download some sample databases (ans a useful doc file)
You can't beat a real working example. I have a database (somewhere) with a many-many relationship.
I'll post it on Monday.
in your context, is a 'Scanner' a person, or a device (not that it really matters).
Normally, when I need to do this, I'll use a combo box, and two listboxes. In the combo box, I'll display either the Contract, or the Scanner. For this example, lets assume that we are using Contract. Then, in the listbox on the left (lstAssigned), I'll display the Scanners that are mapped to that contract with a query that looks something like:
SELECT S.ID, S.Name
FROM tbl_Scanners as S
INNER JOIN tbl_ContractScanners CS
ON S.ScannerID = CS.ScannerID
WHERE CS.ContractID = Forms!yourFormName.cboCont
Then, in the listbox on the right (lstUnassigned), I display the list of Scanners that are not mapped to that contract, with a query that looks something like:
SELECT S.ID, S.Name
FROM tbl_Scanners as S
LEFT JOIN tbl_ContractScanners CS
ON S.ScannerID = CS.ScannerID
WHERE NZ(CS.ContractID, 0) <> Forms!yourFormName.cboCont
This 2nd query should identify all of the scanners, not assigned to the contract displayed in your Contract combo box.
Then, I include a couple of buttons (positioned between the two lists boxes) which allow the user to assign the scanner to the contract or to remove the scanner from the contract. These buttons start out being disabled and are enabled / disabled in the Click event of each of the listboxes. If the users clicks into the left listbox, then I enable the Remove button and disable the Add button. Reverse that for the listbox on the right (scanners not assigned).
Then, the code behind the Add button looks something like:
Normally, when I need to do this, I'll use a combo box, and two listboxes. In the combo box, I'll display either the Contract, or the Scanner. For this example, lets assume that we are using Contract. Then, in the listbox on the left (lstAssigned), I'll display the Scanners that are mapped to that contract with a query that looks something like:
SELECT S.ID, S.Name
FROM tbl_Scanners as S
INNER JOIN tbl_ContractScanners CS
ON S.ScannerID = CS.ScannerID
WHERE CS.ContractID = Forms!yourFormName.cboCont
Then, in the listbox on the right (lstUnassigned), I display the list of Scanners that are not mapped to that contract, with a query that looks something like:
SELECT S.ID, S.Name
FROM tbl_Scanners as S
LEFT JOIN tbl_ContractScanners CS
ON S.ScannerID = CS.ScannerID
WHERE NZ(CS.ContractID, 0) <> Forms!yourFormName.cboCont
This 2nd query should identify all of the scanners, not assigned to the contract displayed in your Contract combo box.
Then, I include a couple of buttons (positioned between the two lists boxes) which allow the user to assign the scanner to the contract or to remove the scanner from the contract. These buttons start out being disabled and are enabled / disabled in the Click event of each of the listboxes. If the users clicks into the left listbox, then I enable the Remove button and disable the Add button. Reverse that for the listbox on the right (scanners not assigned).
Then, the code behind the Add button looks something like:
Private sub cmdAddScanner
Dim strSQL as string
strSQL = "INSERT INTO tbl_ContractScanners(ContractID, ScannerID) " _
& "Values (" & me.cboContract & ", " & me.lstUnAssigned & ")"
currentdb.execute strsql, dbfailonerror
me.lstAssigned.Requery
me.lstUnassigned.Requery
me.cmdAdd.Enabled = false
End Sub
Private Sub cmdRemoveScanner
Dim strSQL as string
strSQL = "DELETE FROM tbl_ContractScanners " _
& "WHERE ContractID = " & me.cboContract _
& "AND ScannerID = " & me.lstAssigned
Currentdb.Execute strSQL, dbfailonerror
me.lstAssigned.requery
me.lstUnAssigned.requery
me.cmdRemove.Enabled = false
End Sub
NO, NO, NO!
Do not use multi-valued fields if you ever want to query on those values.
I do not know a single professional Access developer that would encourage you to use those. Learn the proper techniques for dealing with many to many relationships.
Do not use multi-valued fields if you ever want to query on those values.
I do not know a single professional Access developer that would encourage you to use those. Learn the proper techniques for dealing with many to many relationships.
NO, NO, NO!I agree totally and I don't recommend them.
Do not use multi-valued fields if you ever want to query on those values
They are handy for amateurs etc (as are macros) or quick simple databases ..... and are very hard to step back from without a database redesign.
Thanks for the quick response, its now Sunday morning here and I trying Dale's suggestion of a combo box and two listboxes. This works very well.
I've added a button which loads a notinlist form to enable scanners (name and a serial no) to added.
I have two issues:
When I'm adding a "new" contract; at the point I try to add a scanner it reports an error "You cannot add or change a record because a related record is required in table 'tblContracts'" . Any thoughts on an efficient way to handle this ?
Having added a scanner to the tblScanners these do not show in the lstUnassigned - so I need to modify the query to include these scanners !
I am browsing Eirman's list of bits & pieces this is proving interesting.
Thanks
Brian
I've added a button which loads a notinlist form to enable scanners (name and a serial no) to added.
I have two issues:
When I'm adding a "new" contract; at the point I try to add a scanner it reports an error "You cannot add or change a record because a related record is required in table 'tblContracts'" . Any thoughts on an efficient way to handle this ?
Having added a scanner to the tblScanners these do not show in the lstUnassigned - so I need to modify the query to include these scanners !
I am browsing Eirman's list of bits & pieces this is proving interesting.
Thanks
Brian
I don't generally use that same form to add contracts or scanners to those tables, although you could use the NotInList event to do so for the Contracts.
My combo box assumes that you have a ContractID and a ContractTitle in the combo rowsource, and that the ContractID is the bound column, usually hidden.
My combo box assumes that you have a ContractID and a ContractTitle in the combo rowsource, and that the ContractID is the bound column, usually hidden.
My Combo is set up as you suggest.
Your approach works well for updates. I can also add a new contract firstly, without scanners, then update the contract with scanners later.
On adding a new contract with scanners I would (I think) have a similar issue with another form.
I think I need to save the new contract record just before adding a scanner (some DAO command built into the add and delete buttons ?) but this may need undoing if user cancelled the addition of the contract.
Else
delay all updates till user completes the addition of the scanners and then add the contract then the links.
How do you handle this situation ?
Your approach works well for updates. I can also add a new contract firstly, without scanners, then update the contract with scanners later.
On adding a new contract with scanners I would (I think) have a similar issue with another form.
I think I need to save the new contract record just before adding a scanner (some DAO command built into the add and delete buttons ?) but this may need undoing if user cancelled the addition of the contract.
Else
delay all updates till user completes the addition of the scanners and then add the contract then the links.
How do you handle this situation ?
I would generally have three separate forms, all three of which would be accessed from some form of a switchboard, and the ContractScanners form would also be available from both the Contracts, and Scanners forms.
1. Contracts: this form would be used to add all the necessary details regarding the contracts. There would be a button or popup menu on this form that would allow me to get to the ContractScanners form, but only when the Contracts form is not a NewRecord. Until the record is saved, the ContractScanners button or option would be disabled.
2. Scanners: this form would be used to add all the necessary details regarding the scanners. There would be a button or popup menu on this form that would allow me to get to the ContractScanners form, but only when the Scanners form is not a NewRecord. Until the record is saved, the ContractScanners button or option would be disabled.
3. ContractScanners: This form would allow you to select any Contract from a combo box on the form. If the form is opened from within the Contracts form, then the combo box would default to the contract that is currently displayed on the Contracts form. Whenever the combo box changes, the two lists (lstAssigned and lstUnassigned would be requeried.
1. Contracts: this form would be used to add all the necessary details regarding the contracts. There would be a button or popup menu on this form that would allow me to get to the ContractScanners form, but only when the Contracts form is not a NewRecord. Until the record is saved, the ContractScanners button or option would be disabled.
2. Scanners: this form would be used to add all the necessary details regarding the scanners. There would be a button or popup menu on this form that would allow me to get to the ContractScanners form, but only when the Scanners form is not a NewRecord. Until the record is saved, the ContractScanners button or option would be disabled.
3. ContractScanners: This form would allow you to select any Contract from a combo box on the form. If the form is opened from within the Contracts form, then the combo box would default to the contract that is currently displayed on the Contracts form. Whenever the combo box changes, the two lists (lstAssigned and lstUnassigned would be requeried.
To sum up what the others have said; Access can't model with forms a M-M relationship directly. You only can do a Parent / child (one to many) relationship with a main/subform combination.
So for a M to M, you need two and in the subform, you include a combo to the other side of the M to M.
And no points please....I just want to sum up what was said.
Jim.
So for a M to M, you need two and in the subform, you include a combo to the other side of the M to M.
And no points please....I just want to sum up what was said.
Jim.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial