• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

Recursion using only queries?

Hello - I have to concatenate contact names associated with given client accounts for use in a mail merge routine. Is there a way to do this using only queries?

This is in an Access 2003 mde, for which I do not have the mdb (don't judge me!), so I can't add new VBA routines...

So, for an AcctNumber of "1", with 3 contacts, I can pull ContName by AcctNumber, and get:
1 Alex Adams
1 Bob Barker
1 Clyde Carter

Is there a way I can get to:
1 Alex Adams, Bob Barker, Clyde Carter
using only queries?

If I could just add a running counter to the 1st pull above, I could come off of that with a cross-tab query, forced to a fixed set of columns, and then just concatenate the names in the columns in a 3rd query. But I don't know how to generate a running counter in a query without using VBA.

So, any help with either of these problems would be very much appreciated


  • 2
1 Solution
Nico BontenbalCommented:
You might be able to get the count by using a dcount function. If you add a column with something like:
Dcount("*","Contact","ContName<=" & ContName)
This would count then number of contacts smaller or equal to the current contact. If you sort by ContName in the query this is essentially the row number.
See also this link:
Have you thought of linking to your MDE from a new MDB, so that you can then add VBA procedures?
Can you post a sample database too?
mlagrangeAuthor Commented:
Woo-Hoo - Thanks Nicobo! That works like a charm!

Jez - all you could really link to would be the tables, right?  This is a big production app, used by about 50 users, and I'm trying to tweak this new requirement in there in the easiest way possible.

Thanks again

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now