Unanswered: single query field to list all related items?
I'm looking for a way to persuade Access 2000 to create a "total" of sorts for a text field. I'm querying tables with all U.S. zip codes and a list of our business units. The query is set up to "Group By" zip code and "Sum" a couple numeric business unit characteristics, and it works fine. Right now I'm setling for the ID of the "First" business unit in each zip, but what I'd like is a text field that concatenates the ID's of all the business units active in each zip code (usually one, but sometimes more than three or four). I'm guessing this involves use of DLookup or some VBScript recordset-looping code, but I could use a kick in the right direction. Any ideas?
Each zip code in the ZipCodes table may have several related records in the "Areas" table, joined one-to-many on the Zip field; my totals query currently looks like this:
ZipCodes!Zip - GroupBy
Areas.AreaNum - First
Areas.WorkSites - Sum
Areas.BudgetTotal - Sum
Areas.ExpenseTotal - Sum
I'd like to generate a text field in place of Areas.AreaNum that lists the ID's of all the Areas working in each zip. So instead of getting the name only for the first related record (such as "Area1"), when there is more than one area I'd like to get the field with "Area1, Area5, Area7" with the names of all the related records (there are never more than four or five). The purpose is to label zip code maps with the list of areas active in each zip.