Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Location
    Durham, NC
    Posts
    3

    Unanswered: single query field to list all related items?

    Hi folks,

    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?

    Thanks in spades,
    N

  2. #2
    Join Date
    Nov 2001
    Posts
    336
    If you could post more details or example of what you need, it would be easier to help you.

  3. #3
    Join Date
    Jan 2002
    Location
    Durham, NC
    Posts
    3
    OK, will do, thanks.

    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

    etc.

    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.

    Is that clearer? Thanks!
    N

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •