Consolidating text from multiple rows

G

Guest

Can anyone give a hint on how to approach consolidating text from multiple
rows into a single cell? For example, in my table I have 2 fields, the first
is the vendor ID and the second is the text descriptions of the commodities
they provide.

VENDOR_ID COMM_DESCRIPTION
1 ABRASIVES, SANDBLASTING
1 SOIL EROSION SHEETING MATERIAL
1 BRUSH CHIPPER, CUTTER AND SAW PARTS
2 PUMICE STONE
2 ABRASIVES, SANDBLASTING
3 CONSOLES, POLICE VEHICLE
3 AIRCRAFT AVIONICS
3 CONSOLES, HEAVY TRUCK

How would I approach a query where it would give me my list of IDs in one
field and a consolidated list of their commodities in another, but all in a
single row...

1 ABRASIVES, SANDBLASTING, SOIL EROSION SHEETING MATERIAL,
BRUSH CHIPPER, CUTTER AND SAW PARTS
2 PUMICE STONE, ABRASIVES, SANDBLASTING
3 CONSOLES, POLICE VEHICLE, AIRCRAFT AVIONICS, CONSOLES, HEAVY TRUCK
 
D

Dirk Goldgar

Kiraly said:
Can anyone give a hint on how to approach consolidating text from
multiple rows into a single cell? For example, in my table I have 2
fields, the first is the vendor ID and the second is the text
descriptions of the commodities they provide.

VENDOR_ID COMM_DESCRIPTION
1 ABRASIVES, SANDBLASTING
1 SOIL EROSION SHEETING MATERIAL
1 BRUSH CHIPPER, CUTTER AND SAW PARTS
2 PUMICE STONE
2 ABRASIVES, SANDBLASTING
3 CONSOLES, POLICE VEHICLE
3 AIRCRAFT AVIONICS
3 CONSOLES, HEAVY TRUCK

How would I approach a query where it would give me my list of IDs in
one field and a consolidated list of their commodities in another,
but all in a single row...

1 ABRASIVES, SANDBLASTING, SOIL EROSION SHEETING
MATERIAL, BRUSH CHIPPER, CUTTER AND SAW PARTS
2 PUMICE STONE, ABRASIVES, SANDBLASTING
3 CONSOLES, POLICE VEHICLE, AIRCRAFT AVIONICS, CONSOLES, HEAVY TRUCK
Click to expand...

See the fConcatChild function posted on The Access Web at:

http://www.mvps.org/access/modules/mdl0004.htm
Return a concatenated list of sub-record values

You would build a query that calls the function for each vendor.
 
Top