Non-limited table relationships

[Ben M Rowe]

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.

 

[Jeff Boyce]

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.

 

[KARL DEWEY]

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.

 

[Ben M Rowe]

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