Link Items in Same Table
in [Getting Started]
|
Prev: Access 2000 Slowdown
Next: access 2007 combo boxes
From: TheScullster on 11 Mar 2010 04:23 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
From: John Spencer on 11 Mar 2010 08:21 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 > > > >
From: TheScullster on 11 Mar 2010 08:55 "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
From: John Spencer on 13 Mar 2010 08:05 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 > >
From: TheScullster on 16 Mar 2010 13:02
"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 |