Query multiple tables using INNER JOIN and multivalued fields

[vman]

I have no clue on what/where to look for my requirement, however, I have done some reading about INNER JOIN and it looks like that is something I need to use, but, I am not sure how. Here is my current table structure (I have omitted some fields from this example and have given some sample data in italics to make the table structure more clear. tblEmployees ID (autonumber) 3 EmployeeName John EmployeePhone 555999555 EmployeeLocation New York tblClients ClientID (autonumber) 1 , 2 , 3 ClientName ABC Company , XYZ Company, PQR Company fkeyLocationID tblLocations LocationID 1 , 2 Location New York , Chicago tblEmployeeClients (junction table) fkeyID 3 EmployeeClients (multivalued number) 1,2 The junction table tblEmployeeClients only stores ID of the Employee and in the second column (which is a multi-valued field), the ID of each of the clients the employee Supports. I am trying to generate a report that lists say, EmployeeName alongside the clients supported by the Employee (listing the client location is not required, however, it would be good to know how to do that as well). The report (for the example above), should look like this: Name Clients Supported John ABC Company, PQR Company Currently, I am able to get : John 1, 2 i.e the client ID for the clients that the employee supports instead of the corresponding company names. Hope, someone here can help me with this requirement.

[June7]

So you are saving the client ID in multi-value field and now you want to show the client name in report? I NEVER use multi-value fields because of the issues involved. If you want the client name to display on one row in report as you show, then need to save the client name in the multi-value field, not the client ID. Can't link the client info table to the client IDs in the multi-value field unless you first do a query that expands the multi-value field so that each value is a separate record. The result of that query is like the child table the data should have been saved in to begin with. Review: http://office.microsoft.com/en-us/ac...001233722.aspx