ConcatRelated() in Access, using two keys

Question

I found the ConcatRelated() function [http://allenbrowne.com/func-concat.html] and it seems to do what I want it to, but I need to feed in two keys rather than just one.

My data looks like:

Chain    Store      Warehouse
Walmart  Tucson     Phoenix
Walmart  Tucson     Flagstaff
Walmart  Phoenix    Phoenix
Bi-Lo    Phoenix    Phoenix
Bi-Lo    Phoenix    Flagstaff

And I need to concatenate based on both the Chain and the Store:

Chain    Store      Warehouses
Walmart  Tucson     Phoenix, Flagstaff
Walmart  Phoenix    Phoenix
Bi-Lo    Phoenix    Phoenix, Flagstaff

Can you help me with the arguments I need to use within the brackets?

Have a look at the ADO version sketched here: http://stackoverflow.com/questions/92698/combine-rows-in-access-2007/93863#93863 — Fionnuala, Dec 11 '12 at 15:12

score 2 · Accepted Answer · answered Dec 11 '12 at 15:22

2

You could write it this way:

SELECT Distinct
  tbl.Chain,
  tbl.Store,
  ConcatRelated("Warehouse","tbl","Chain=""" & [Chain] & """ and Store=""" & [Store] & """") AS Warehouses
FROM tbl;

answered Dec 11 '12 at 15:22

fthiella

48,073
15
90
106

1

That's solved that problem, thank you. :) I'm running this on another query, rather than another table - does that mean that the base query is run each time the ConcatRelated function is called? It is currently very very very slow – Wilskt Dec 11 '12 at 16:35
I'm guessing yes, because I tried it with a table-version of the data and it took a few seconds. Not sure what to do about this - if it helps, the actual green 'query in progress' bar disappears very quickly, it's only when it comes to displaying the data on screen that it gets stuck. Not sure what to do about this....... – Wilskt Dec 11 '12 at 16:46

ConcatRelated() in Access, using two keys

1 Answers1

Linked

Related