Hello, I have designed a Totals query that has produced multiple rows of data. All of the information that I want is in the resulting dataset, but there are several records that have repeated entries in them.
The fields are: ID, Division, Category, Person, Service and Location. 20 records result, with all of the data in the first three fields being the same. The last two fields have the unique data, with five kinds of Service listed across four Locations (e.g., to produce 20 records).
I would like to create a resulting dataset that shows one, combined Location field for any one Service, thereby resulting in a dataset of five total records. As an example, if I have Service 1, Service 2, Service 3, Service 4, and Service 5, I would like to show each Service on separate lines, but with a corresponding combined (I suppose concatenated) field showing the corresponding locations in one field separated by commas. For example, instead of five different lines for Service 1 I would have one line for Service 1 with a corresponding field that looks like "Location 1, Location 2, Location 3, Location 4".
I have to believe this is possible, but have been wracking my brain and can't figure it out! Can you help?