If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Link Items in Same Table
Ok so I have a table which contains contract numbers and associated data
records. How do I create a table and populate it to store link information between selected records in the above table? Table structure Ac-id - PK autonumber Account ID Field Ct-code number Conract Number Ac-title text Contract description Ca-code text customer address So what I want to do is record any commonality between 2 or more records in the above table. For instance it may be that 2 or more contracts occur on the same site. So I need a mechanism to store the contract "links" and also a memo field to provide information on the way in which the contracts are connected. Any advice/starter info appreciated Phil |
#2
|
|||
|
|||
Link Items in Same Table
It sounds as if you need at least one additional table.
LinkedContracts AccountID number type long AssociatedAccountID number type long Association text (or memo) This table would have the two accounts and the reason they are associated. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County TheScullster wrote: Ok so I have a table which contains contract numbers and associated data records. How do I create a table and populate it to store link information between selected records in the above table? Table structure Ac-id - PK autonumber Account ID Field Ct-code number Conract Number Ac-title text Contract description Ca-code text customer address So what I want to do is record any commonality between 2 or more records in the above table. For instance it may be that 2 or more contracts occur on the same site. So I need a mechanism to store the contract "links" and also a memo field to provide information on the way in which the contracts are connected. Any advice/starter info appreciated Phil |
#3
|
|||
|
|||
Link Items in Same Table
"John Spencer" wrote It sounds as if you need at least one additional table. LinkedContracts AccountID number type long AssociatedAccountID number type long Association text (or memo) This table would have the two accounts and the reason they are associated. Thanks John I'd kind of got this far (in my head) but I'm not sure how to "link" this table to the existing one. Is your AccountID field a foreign key linked to ac-id from the existing table? Also, is your AssociatedAccountID field a foreign key linked to a different ac-id from the existing table? Existing table Table structure Ac-id - PK autonumber Account ID Field Ct-code number Conract Number Ac-title text Contract description Ca-code text customer address Finally what would be the best mechanism to allow for user selection of the associated records and populating the new table? Thanks for your help Phil |
#4
|
|||
|
|||
Link Items in Same Table
Sorry,
I lost this thread. I keep pondering on this. I don't really like this structure but I am stuck on coming up with a better structure. This table is linked to the existing one on two fields. I would probably link to the AccountID field in most cases. Then use a form plus subform structure to enter the information. When you need to extract information for reports, you would need to have two references to the existing table in the query. You would link one reference to AccountID and the other reference to the AssociatedAccountID. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County TheScullster wrote: "John Spencer" wrote It sounds as if you need at least one additional table. LinkedContracts AccountID number type long AssociatedAccountID number type long Association text (or memo) This table would have the two accounts and the reason they are associated. Thanks John I'd kind of got this far (in my head) but I'm not sure how to "link" this table to the existing one. Is your AccountID field a foreign key linked to ac-id from the existing table? Also, is your AssociatedAccountID field a foreign key linked to a different ac-id from the existing table? Existing table Table structure Ac-id - PK autonumber Account ID Field Ct-code number Conract Number Ac-title text Contract description Ca-code text customer address Finally what would be the best mechanism to allow for user selection of the associated records and populating the new table? Thanks for your help Phil |
#5
|
|||
|
|||
Link Items in Same Table
"John Spencer" wrote Sorry, I lost this thread. I keep pondering on this. I don't really like this structure but I am stuck on coming up with a better structure. This table is linked to the existing one on two fields. I would probably link to the AccountID field in most cases. Then use a form plus subform structure to enter the information. When you need to extract information for reports, you would need to have two references to the existing table in the query. You would link one reference to AccountID and the other reference to the AssociatedAccountID. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Thanks John I think I follow your instructions here. One element that is not clear is the means of creating the record entries in the new table. Yes this probably sounds like the dumbest of basic questions, but so far my understanding has been limited to data entry into a single table via a form. What we are trying to achieve here is to create an entry in our new table linking to a record in an existing table. How is that best achieved? I pondered a "create link" button with some code behind to capture the appropriate contract record from the form you describe and then present the user with a text box to add the memo. How this is done is almost a mystery. Thanks Phil |
#6
|
|||
|
|||
Link Items in Same Table
Phil:
Inserting a row into the LinkedContracts table is quite easily achieved by means of a subform based on this table. This subform would be embedded in a form based on the Contracts table with the LinkMasterFields property set to Ac-id and the LinkChildFields property set to AccountID. The subform would contain two controls, a combo box bound to AccountID and a text box bound to Association. The combo box would be set up as follows: RowSource: SELECT [Ac-id], [Ct-code] FROM [Contracts] ORDER BY [Ct-code]; BoundColum: 1 ColumnCount: 2 ColumnWidths: 0cm;8cm If your units of measurement are imperial rather than metric Access will automatically convert the last one. The important thing is that the first dimension is zero to hide the first column and that the second is at least as wide as the combo box. However, its important to understand that the relationship between the two contracts linked in this way is directional, so the subform will only show those contacts which have been linked to the current contact in the parent form, not any contacts to which the current contact in the parent form has been linked. Similarly if you create a report based on a query which joins Contacts to LinkedContracts on AccountID and LinkedContracts to another instance of Contracts on AssociatedAccountID the report will only show the links in the one direction. You can get it to show links in both directions by creating another query which joins Contacts to LinkedContracts on AssociatedAccountID and LinkedContracts to another instance of Contracts on AccountID, and then basing the report on a union query of these two queries. This is fine if you are only interested in immediate links between contracts, but it gets more complicated if you want to return all links down the line, e.g. if Contract A links to Contract B which in turn links to Contracts D and E. This is not too difficult if the number of steps is finite, but otherwise it’s akin to the classic ‘bill of materials’ problem, which requires a more elaborate solution. You’ll fund a demo of ways of handling this in the file attached to my post at: http://community.netscape.com/n/pfx/...apps&tid=23133 Ken Sheridan Stafford, England TheScullster wrote: Sorry, I lost this thread. [quoted text clipped - 14 lines] The Hilltop Institute University of Maryland Baltimore County Thanks John I think I follow your instructions here. One element that is not clear is the means of creating the record entries in the new table. Yes this probably sounds like the dumbest of basic questions, but so far my understanding has been limited to data entry into a single table via a form. What we are trying to achieve here is to create an entry in our new table linking to a record in an existing table. How is that best achieved? I pondered a "create link" button with some code behind to capture the appropriate contract record from the form you describe and then present the user with a text box to add the memo. How this is done is almost a mystery. Thanks Phil -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201003/1 |
#7
|
|||
|
|||
Link Items in Same Table
"KenSheridan via AccessMonster.com" wrote Inserting a row into the LinkedContracts table is quite easily achieved by means of a subform based on this table. This subform would be embedded in a form based on the Contracts table with the LinkMasterFields property set to Ac-id and the LinkChildFields property set to AccountID. The subform would contain two controls, a combo box bound to AccountID and a text box bound to Association. The combo box would be set up as follows: RowSource: SELECT [Ac-id], [Ct-code] FROM [Contracts] ORDER BY [Ct-code]; BoundColum: 1 ColumnCount: 2 ColumnWidths: 0cm;8cm If your units of measurement are imperial rather than metric Access will automatically convert the last one. The important thing is that the first dimension is zero to hide the first column and that the second is at least as wide as the combo box. However, its important to understand that the relationship between the two contracts linked in this way is directional, so the subform will only show those contacts which have been linked to the current contact in the parent form, not any contacts to which the current contact in the parent form has been linked. Similarly if you create a report based on a query which joins Contacts to LinkedContracts on AccountID and LinkedContracts to another instance of Contracts on AssociatedAccountID the report will only show the links in the one direction. You can get it to show links in both directions by creating another query which joins Contacts to LinkedContracts on AssociatedAccountID and LinkedContracts to another instance of Contracts on AccountID, and then basing the report on a union query of these two queries. This is fine if you are only interested in immediate links between contracts, but it gets more complicated if you want to return all links down the line, e.g. if Contract A links to Contract B which in turn links to Contracts D and E. This is not too difficult if the number of steps is finite, but otherwise it's akin to the classic 'bill of materials' problem, which requires a more elaborate solution. You'll fund a demo of ways of handling this in the file attached to my post at: http://community.netscape.com/n/pfx/...apps&tid=23133 Wow - thanks for such a comprehensive response Ken. Also for pointing out the vagueries/intrecacies of the undertaking. I believe that nested links will not be required, but bi-directional links will. So my final solution will be based on your penultimate paragraph. As I have been a while away from all this, I will start from the beginning of your response and try to create the different options that you have outlined. Thanks again - your assistance is greatly appreciated Phil |
Thread Tools | |
Display Modes | |
|
|