Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Collecting within a Query (2003 SP2)

    This will probably have to be relegated to the wish list.
    I have a list of Meetings (Meets). Each Meet has 0 or more Tasks. Each Task can have 0 or more Helpers. I need to collect all the Helpers for a given Meet & Task into a query field. Currently I am using code to collect all the Helpers, but the Report that uses this query takes minutes to open, and I am searching for ways to speed it up.
    Is there a way to collect all the Helpers for a given Meet & Task using a query? I already have a query (qryHelpsForMeet) that lists Helpers next to the MeetID and TaskID, but this shows one line per Helper per Task per Meet, rather than one line per Task showing all Helpers for that Meet & Task.
    Regards,
    Peter

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Collecting within a Query (2003 SP2)

    What exactly would you like the result to be?

    A long string of helpers, separated by (for example) comma's? If so, you could creater a new query based on qryHelpsForMeet and use the Concat function from <post#=301,393>post 301,393</post#>.

    If you want something else, please provide more details.

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Collecting within a Query (2003 SP2)

    Thanks for that Hans. What
    Regards,
    Peter

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Collecting within a Query (2003 SP2)

    Whichever method you use, you'll be looping through a recordset many times. Depending on the size of the tables involved, this is bound to take some time. The function from the post I referred to is slightly more efficient than yours because opening a recordset based on an SQL statement with a WHERE clause is faster than using the Filter property, but I don't think the difference will be spectacular.

    If the data are relatively stable, it might pay to store the concatenated names in a table, and update this table only when needed. You could then use this table for your report.

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Collecting within a Query (2003 SP2)

    Storing the concatenated names in a table was my last resort. But it looks like it is my only option.
    Thanks again 50K man.
    Regards,
    Peter

Posting Permissions

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