Re: Many-to-Many relationship Query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Glenn,

I think this will do what you want.

First, my example Tables look like these. In the first one, the key value is a text string and not a number. (This distinction will be important later, in the function that matches its value with another Table.)

[Customer] Table Data*** View:

  Customer_ID  Customer
  -----------  ----------------
  001          Joe Bloggs Ltd
  007          Bond, James
  123          Acme Explosives


In the following Table, the primary key, [Employee_ID], is an Autonumber field.

[Employee] Table Data*** View:

  Employee_ID  Employee
  -----------  --------
  -1911156243  Jim
  -1516216861  Sarah
  -560162532   Glenn
  1748767914   Harry

I usually apply Lookup properties to all foreign keys, but please remember that even though they look different using Lookups, the values actually stored in the Tables are the key values.

[Cust-Empl] Table Data*** View (without Lookup properties):
  Customer_ID  Employee_ID
  -----------  -----------
  001          -1911156243
  001          -1516216861
  123          -1516216861
  001          -560162532
  001          1748767914
  123          1748767914

This is not very pretty, so after applying Lookup properties to the two foreign keys, the table looks like this:

[Cust-Empl] Table Data*** View:

  Customer_ID        Employee_ID
  --------------     -----------
  Joe Bloggs Ltd     Jim
  Joe Bloggs Ltd     Sarah
  Acme Explosives    Sarah
  Joe Bloggs Ltd     Glenn
  Joe Bloggs Ltd     Harry
  Acme Explosives    Harry

The Query that I think you have already defined in your database looks something like this.

[Q01_EmployeesByCustomer] SQL:

  SELECT Customer.Customer_ID,
  Customer.Customer, Employee.Employee
  FROM Employee
  INNER JOIN (Customer INNER JOIN [Cust-Empl]
  ON Customer.Customer_ID = [Cust-Empl].Customer_ID)
  ON Employee.Employee_ID = [Cust-Empl].Employee_ID
  ORDER BY Customer.Customer, Employee.Employee;

.... and it produces a list with repeating customer names.

[Q01_EmployeesByCustomer] Query Data*** View:

  Customer_ID  Customer          Employee
  -----------  ----------------  --------
  123          Acme Explosives   Harry
  123          Acme Explosives   Sarah
  001          Joe Bloggs Ltd    Glenn
  001          Joe Bloggs Ltd    Harry
  001          Joe Bloggs Ltd    Jim
  001          Joe Bloggs Ltd    Sarah

The following function reads this Query and produces a list of employee names similar to what you asked for. Instead of having it call the named Query, [Q01_EmployeesByCustomer], as it does, you might choose to put the entire SQL of [Q01_EmployeesByCustomer] into this code. However, depending on how you prefer to maintain your database, doing it as I suggest here allows you to maintain the Query in Query Design View, and the only SQL you have to look at is the filter, and the filter is pretty short.

  'List all employees for the specified customer
  Public Function EmpByCust( _
    CustomerID As String) As String

    'Unfiltered Query
    Dim rsEmployeesByCustomer As DAO.Recordset

    'Filtered to return only those matching the argument
    Dim rsFiltered As DAO.Recordset

    'Name of unfiltered Query
    Const strQ01 As String = "Q01_EmployeesByCustomer"

    'Value to be returned, such as
    ' "Glenn; Harry; Jim; Sarah"
    Dim strResult As String


    Set rsEmployeesByCustomer = _
          CurrentDb.OpenRecordset( _
          strQ01, dbOpenSnapshot)

    With rsEmployeesByCustomer
      .Filter = "Customer_ID = '" _
            & CustomerID & "'"
      .MoveLast

      Set rsFiltered = .OpenRecordset(dbOpenSnapshot)

    End With 'rsEmployeesByCustomer

    With rsFiltered

      If .BOF Then
        EmpByCust = "<Nobody>"
        Exit Function
      End If '.BOF ...

      strResult = .Fields("Employee")
      .MoveNext

      While Not .EOF
        strResult = strResult & "; " _
              & .Fields("Employee")
        .MoveNext
      Wend 'Not .EOF ...

    End With 'rsEmployees

    EmpByCust = strResult

  End Function 'EmpByCust()


And the following Query invokes this function to produce the list you wanted.

[Q02_EmployeeLists] SQL:

  SELECT Customer.Customer_ID, Customer.Customer,
  EmpByCust([Customer]![Customer_ID]) AS Employees
  FROM Customer
  ORDER BY Customer.Customer_ID;


[Q02_EmployeeLists] Query Data*** View:

  Customer_ID   Customer           Employees
  -----------   ----------------   ------------------------
  001           Joe Bloggs Ltd     Glenn; Harry; Jim; Sarah
  007           Bond, James        <Nobody>
  123           Acme Explosives    Harry; Sarah


  -- Vincent Johns <vjohns@xxxxxxxxxxxxxxxxxx>
  Please feel free to quote anything I say here.


Glenn Brown wrote:

Thanks for your reply. I should have explained myself a little better. I've already done what you suggested. That part is working fine.

I want a query showing all the Customers with one calculated field that shows all the related Employee's. So Joe Bloggs Ltd (say customer 1) will have a calculated field called Employees with say "Glenn; Jim; Harry; Sarah" in it. These are obviously four separate Employees that are linked to this customer. Some code needs to be written to get this I think? The code bit is what I need.

I know I can create a inner join between the two tables to get a list of related Employee's. The problem with this is - if the Customer is linked to two Employee's, the Customer will be listed twice in the Query. This isn't want I want.

Regards,
Glenn

"Vincent Johns" wrote:

[...] 

Glenn Brown wrote:


I've got a database with a many-to-many relationship between two tables (Employees and Customers). By this I mean I have a table that sits in-between both tables and records the relationship. I want to create a query which lists all the Customers with a calculated field that lists all the Employees within it.

For example
Customer ID    Customer	Employee’s
001	      Joe Bloggs Ltd	Glenn; Jim; Harry; Sarah

Does anyone have any ideas? Any help would be appreciated.

Regards,
Glenn Brown

.

Quantcast