1

I have 2 tables:

  • Contacts
  • Helpers

The Helper field (which is linked to the Helper table) in Contacts is a multi-valued field so more than one helper can be selected and displayed in a report.

Each Helper is linked to more than 1 Contact.

I'm trying to create a query selecting all contacts for one helper, and display all helpers for the resulting contacts. I think I am off-base with multi-valued fields, but cannot find another solution.

It is in Access 2010. Tables do not have multi-value fields. They are:

Contacts

ContactID
Surname
Email

Helper

HelperID
Name

closed as unclear what you're asking by ypercubeᵀᴹ, dezso, Colin 't Hart, Paul White, Marian Jul 1 '15 at 13:05

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

3

This is a typical situation in which you have to create three tables: Helpers, Contacts, and Help_Contact, using the third table to represent associations between the elements of the first two, by storing the foreign keys for the first two, without any multivalued field in those.

That is, you should have something like:

Helpers(h_primary_key, other_fields_only_relevant_to_helpers)
Contacts(c_primary_key, other_fields_only_relevant_to_contacts)
Help_contact(h_primary_key,c_primary_key)

So, to display information for contacts or helpers associated to another element, you could use a join:

SELECT relevant_info
FROM Helpers h, Contacts c, Help_contact hc 
WHERE hc.h_primary_key = h.h_primary_key AND hc.c_primary_key = c.c_primary_key AND other_conditions_on_helper_or_contact_or_both
0

It is not very clear from your description a) what DB you are using? b) do you need help in the design of tables? c) or you need help in SELECT query only? But then please show your tables.

So far it is clear only that you have 2 entities Contacts and Helpers, linked as M:M, so Renzo is correct suggesting a third link-table.

Let you want to start dance from some Helper record X.

This query will give you a list of Contacts linked to Helper X:

SELECT Contacts.ID
FROM Contacts JOIN CH      ON Contacts.ID = CH.ContactsPtr 
              JOIN Helpers ON CH.HelpersPtr = Helpers.ID
WHERE Helpers.ID = X

Here CH is link-table with two fields ContactsPtr and HelpersPtr.

Now you want to get for each such Contact all its linked Helpers:

SELECT *
FROM Contacts JOIN CH      ON Contacts.ID = CH.ContactsPtr 
              JOIN Helpers ON CH.HelpersPtr = Helpers.ID 
WHERE Contacts.ID IN 
( SELECT Contacts.ID
  FROM Contacts JOIN CH      ON Contacts.ID = CH.ContactsPtr 
                JOIN Helpers ON CH.HelpersPtr = Helpers.ID
  WHERE Helpers.ID = X
) 

Notes:

1) Result query is of the most general form, which allows you tune it better fryer task, for example instead "SELECT * ", select only useful fields.

2) If it is okay to start with ID of Helpers record only, i.e. you will not have any additional conditions on other fields, you can use sub-query of more simple form using only link-table to get list of IDs:

SELECT *
FROM Contacts JOIN CH      ON Contacts.ID = CH.ContactsPtr 
              JOIN Helpers ON CH.HelpersPtr = Helpers.ID 
WHERE Contacts.ID IN   
      (SELECT ContactsPtr FROM CH WHERE Helpers.ID = X) 
  • Thanks. It is in Access 2010, and need help with the select query. Tables do not have multi-value fields. They are: Contacts ContactID Surname Email Helper HelperID Name – Sue Snyder Jun 27 '15 at 18:02

Not the answer you're looking for? Browse other questions tagged or ask your own question.