Multiple Value Fields and Queries

[nsteenhaut]

Hi,

I have several tables including one called "Contact Events", one called "Attorneys" and one called "Law Offices". My Contact Events table keeps track of meetings between our employees and one or more attorneys. For instance "Contact Events" has a field with staff ID #s that accepts multiple values, it also has a field with attorney_ID #s that accepts multiple values. The idea being that multiple staff members can have a meeting with multiple attorneys. Information about attorneys is listed in a separate table, including an identifier of the law office the attorney is associated with. This Law office, in turn, has its information in yet another table, including the city in which the office is located.



I now want to create a query that shows the information related to these contact events but also want to incllude a field for "city". What's happening is that cases where our employees met say two attorneys, show up in my query as two lines. Basically I need the City-field in my query to store the cities of all the attorneys in that event. Right now, because my "city" field is associated with a table where multiple entries are not enabled/required/useful, I can't get my query to show it on a single line with multiple values.

Any ideas?

[boblarson]

Try reading this:
http://office.microsoft.com/en-us/ac...33722.aspx#BM7

[nsteenhaut]

hi Bob,

Thanks for the swift response. This is not quite the answer I need. I am getting the multi-value fields to query out in a single line - that's not the problem. My issue occurs when adding a new field from another table (in this case "city"). One I add that field in my query design, it splits out my query results in as many rows as I have attorneys (even though the actual attorney field still displays all values).

I have attached a snapshot of the problem. Two employees, met two attorneys based in two cities. If I remove the "City" field from my query design, I get 1 line item - which is what I want. When I add it it, I get two (because there are two attorneys, each one associated with an office, which is located in a certain city). The City field is located in the table relating to the law offices. It is not multivalue. So when I add it in my query, I get separate results.

My goal is to get 1 line item for this example, with the field for "City" being showing values for each of the attorneys in the event (in this case 2 values). Some kind of concatenate function perhaps?

[boblarson]

Some kind of concatenate function perhaps?

Check this out and see if it helps (I hope so because I'm not sure how you would get it otherwise):
http://allenbrowne.com/func-concat.html