Access 97 - Query to condense 1:Many relationship to one line per master record?

From: Patricia Clarke <pclarke_at_hybridturkeys.com>
Date: Thu, 20 Aug 1998 15:52:26 -0400
Message-ID: <6rhulf$qfg$1@flint.sentex.net>

Hi,

I have two tables which are joined in a 1 to Many relationship:

Lab Event

Lab Number
Location
Other stuff

Flock

Lab Number
Flock Number
Other stuff

One Lab Event record can have one or more Flock records.

When I join these tables in a query, I want to summarize each Lab Number in one line (in order to greatly shorten the report, which will otherwise be hundreds of pages long), as follows:

Lab Event

Lab Number 1, Location A
Lab Number 2, Location A
Lab Number 3, Location B

...

Flock

Lab Number 1, Flock 97023
Lab Number 1, Flock 97025
Lab Number 1, Flock 97033
Lab Number 2, Flock 97091
Lab Number 2, Flock 97045
Lab Number 3, Flock 97032

...

I need a query which returns:

Lab Number 1, Location A, Flock 97023/97025/97033
Lab Number 2, Location A, Flock 97091/97045
Lab Number 3, Location B, Flock 97032

...

Exact format isn't important, i.e. whether it shows as 97023/97025/97033 or 97023 97025 97033 or whatever. The point is that I need all flocks for each lab number to appear on the same line.

I'm thinking in terms of somehow concatenating all of the flock numbers into one long field for each lab number, but can't seem to get started.

Any ideas would be greatly appreciated.

Thanks,
Patricia Clarke Received on Thu Aug 20 1998 - 14:52:26 CDT