How to create this query to combine values?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have a table with the following structure and values:

EventID PersonName
----------- ------------
1 John
1 Peter
1 Sylvia
2 John
2 Sylvia
3 Peter
3 June
-------------------------------

I'd like to run a query and get results in the following format:

EventID PersonNames
-------- ---------------
1 John, Peter, Sylvia
2 John, Sylvia
3 Peter, June

Is there a query that will accomplish this?

Thanks for your help.

-amit
 
Hi.
Is there a query that will accomplish this?
Click to expand...

Yes, with a little VBA help. The VBA procedure you need is on the following
Web page:

http://www.mvps.org/access/modules/mdl0008.htm

Copy and paste the fConcatFld( ) function into a code module. Change the
following VBA line of code:

lovConcat = lovConcat & lors(stFldToConcat) & "; "

to:

lovConcat = lovConcat & lors(stFldToConcat) & ", "

.... then save it and compile the code.

Next, create a new query and open the query in SQL View and paste the
following SQL statement into the SQL View pane:

SELECT EventID, fConcatFld("MyTable","EventID","PersonName","Long", EventID)
AS PersonNames
FROM MyTable
GROUP BY EventID;

.... and replace "MyTable" with the name of your table. If the "EventID"
data type isn't Long, then you'll need to replace this in the SQL statement,
too, with whatever data type your field is using.

Save and run the query. Voila! Comma separated list.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.
 
Hi Dale and Gunny,

Thanks for the URL. I implemented the module and the query, and it is
working great!

Cheers,

-amit
 
Hi 69 Camaro, love muscle cars also.
Can u help me? I have same problem the difference it the I need to add
values , example
week saved dropped lost
1 4 2 2
2 6(2new+4 previous week) and so on so forth.
after that I need to show the data in a linear chart by week that shows the
increase, how can I do, tia
 
Back
Top