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
|
|||
|
|||
Non-limited table relationships
Apologies in advance for my basic understanding of access, although I know
what I'm doing with the rest of the office package I'm volunteering with a local group (mainly older generation who have no clue what they're doing at all, with any form of computer) and they've asked me to learn Access and design a new contact database. I believe I have the basics down, but just need a final nudge with a problem. Using Access 2003 I have a number of related tables, but the two in question here are 'contacts' and 'tags'. The contacts table is the obvious (name, address, telephone etc). The 'tags' table are a list of (subjective) identifiers which can be associated with the contacts such as 'brown hair', 'daughter at university', 'needs disabled access' etc etc Now I've got the link working one way perfectly, I have populated the 'tags' table with a list of common tags which are all being perfectly looked up via a one-to-many relationship in the 'contacts' table. The problem is that to assign the tag on the contacts table, it already needs to be present in the tags table. I would like to be able to assign a new tag directly into the 'contacts' table, and have it automatically populated into the 'tags' table. I have seen this working perfectly on other databases, and would just like info on how to get it done here. I've found the 'limit to list' option and tried setting it to no, but it doesn't achieve what I want. I just can't get it to populate back into the tags table, the link seems to be 'one-way'. Apologies for simplistic explanation of what I need, and thanks in advance for any help. |
#3
|
|||
|
|||
Non-limited table relationships
Add and ID field to both of the tables that is an Autonumber.
You need a junction table with [contacts].ID and [tags].ID and any other fields you might need such as Tag_Start and Tag_End date fields. Create a one-to-many relationship between the first two tables and the junction table, selecting Referential Integerity and Cascade Update. Use a form/subform for [contacts]/[tags] with Master/Child links set on the IDs. In the subform use a combo box to select the Tag for the Contact. -- Build a little, test a little. "Ben M Rowe" wrote: Apologies in advance for my basic understanding of access, although I know what I'm doing with the rest of the office package I'm volunteering with a local group (mainly older generation who have no clue what they're doing at all, with any form of computer) and they've asked me to learn Access and design a new contact database. I believe I have the basics down, but just need a final nudge with a problem. Using Access 2003 I have a number of related tables, but the two in question here are 'contacts' and 'tags'. The contacts table is the obvious (name, address, telephone etc). The 'tags' table are a list of (subjective) identifiers which can be associated with the contacts such as 'brown hair', 'daughter at university', 'needs disabled access' etc etc Now I've got the link working one way perfectly, I have populated the 'tags' table with a list of common tags which are all being perfectly looked up via a one-to-many relationship in the 'contacts' table. The problem is that to assign the tag on the contacts table, it already needs to be present in the tags table. I would like to be able to assign a new tag directly into the 'contacts' table, and have it automatically populated into the 'tags' table. I have seen this working perfectly on other databases, and would just like info on how to get it done here. I've found the 'limit to list' option and tried setting it to no, but it doesn't achieve what I want. I just can't get it to populate back into the tags table, the link seems to be 'one-way'. Apologies for simplistic explanation of what I need, and thanks in advance for any help. |
#4
|
|||
|
|||
Non-limited table relationships
Jeff
You're dead right, I do need three tables, absolutely brilliant piece of advice, thank you. Have also switched across to working in forms, have taken your advice and all seems to be working perfectly. Thanks once again to all the advice, really appreciated Ben "Jeff Boyce" wrote: Ben You posted in, and mention, 'tables'. If you are working directly in the tables, stop now! Access tables store data. Access forms display it. Use the forms, Luke! If you use a combobox in a form to allow selection of a 'tag' for a 'contact', you can use the LimitToList property and the NotInList event of that combobox to add new 'tags'. See Access HELP on these two topics ... it even provides example code. Now another consideration... You didn't explicitly state this, so my interpretation may be inaccurate... If a 'contact' can have one or more 'tag's, and if a 'tag' could be used by one or more 'contact's, you need three tables, not two. Your structure might look something like (untested, grossly-oversimplified): tblContact ContactID FName LName ... tlkpTag TagID Tag TagDescription trelContactTag ContactTagID ContactID TagID That third table is how you associate one/more tags with contacts. Use a main form for the contact information, and use a subform for the associated tags. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Ben M Rowe" Ben M wrote in message ... Apologies in advance for my basic understanding of access, although I know what I'm doing with the rest of the office package I'm volunteering with a local group (mainly older generation who have no clue what they're doing at all, with any form of computer) and they've asked me to learn Access and design a new contact database. I believe I have the basics down, but just need a final nudge with a problem. Using Access 2003 I have a number of related tables, but the two in question here are 'contacts' and 'tags'. The contacts table is the obvious (name, address, telephone etc). The 'tags' table are a list of (subjective) identifiers which can be associated with the contacts such as 'brown hair', 'daughter at university', 'needs disabled access' etc etc Now I've got the link working one way perfectly, I have populated the 'tags' table with a list of common tags which are all being perfectly looked up via a one-to-many relationship in the 'contacts' table. The problem is that to assign the tag on the contacts table, it already needs to be present in the tags table. I would like to be able to assign a new tag directly into the 'contacts' table, and have it automatically populated into the 'tags' table. I have seen this working perfectly on other databases, and would just like info on how to get it done here. I've found the 'limit to list' option and tried setting it to no, but it doesn't achieve what I want. I just can't get it to populate back into the tags table, the link seems to be 'one-way'. Apologies for simplistic explanation of what I need, and thanks in advance for any help. . |
Thread Tools | |
Display Modes | |
|
|