Merge Field Values from Different Records for Report with a Comma

[Guest]

Hello all,

We have Access 2000 - old school.

I know enough Access to be dangerous and I'm ok with queries but new to SQL
queries. This problem has me (and others) confused but is probably easy to
do.

I have two tables with a “one to many†relationship. Since I do
environmental work, I'll use Streams and Violations for my example. The
Stream table (parent) has a unique ID with individual stream names. The
stream table is linked using the unique ID to the Violation table which has
separate Violations in each record.

Field Names: Unique_ID StreamName Unique_ID Violation
Value R0001 Pine Creek R0001
Trash

R0001 Pathogen

R0001 Heavy Metal

I need to either create a report or a query (to later make a report) that
will output the data by each stream and then each violation separated by a
comma.

Stream Name Violation
Pine Ceek Trash, Pathogen, Heavy Metal

Any help will be most appreciated!

 

[Guest]

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. You could
use this to concatenate the names.