Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    muliple rows (single field) into string (2000)

    Haven't had my head in a database for ages and my brain seems to have gotten a little rusty.

    I have a database that can be described as follows:
    Main (parent) table of 'Assets', child table of 'Defects'. Aside from IDs the child table has a single field, the 'Defect Type'.
    I have been asked to create a report for the assets which needs to includes the defects combined into a single field (as a comma separated list) rather than using a continous sub report.

    My first thought was this would have to be done by looping through the table for all matching IDs and then concatenating the values from the defect type field into a new created summary field in the asset table.

    Is there a more obvious way to do this just using a query?

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

    Re: muliple rows (single field) into string (2000)

    The code for function named Concat is attached to <post:=301,393>post 301,393</post:>. You can copy this code into a module. Make sure that you have a reference to the Microsoft DAO 3.6 Object Library in Tools | References...

    The post and attachment have some examples of how to use the function, in your case it would probably be something like

    Defects: Concat("Defects", "Defect Type", "[ID] = " & [ID])

    in a query based on Assets, where ID is the ID field, assumed to be numeric.

  3. #3
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: muliple rows (single field) into string (2000)

    Yes, you can do it using an update query:

    Create your new summary field in your Asset table. Then create a query with the following SQL:
    UPDATE tblAsset INNER JOIN tblDefect ON tblAsset.ID = tblDefect.AssetID SET tblAsset.DefectSummary = <!t>[DefectSummary]<!/t> & ', ' & <!t>[Defect type]<!/t>;

    where:<UL><LI>tblAsset is the name of your Asset table<LI>tblDefect is the name of your defects table<LI>DefectSummary is your new summary field<LI><!t>[Defect type]<!/t> is your defect field in your defect table[/list]
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

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

    Re: muliple rows (single field) into string (2000)

    This approach has some disadvantages:
    - It'll concatenate only the first two values.
    - The DefectSummary field will start with ", "
    - You'd have to run the update query each time the Defects records have been edited.
    - Storing calculated values is redundant.

  5. #5
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: muliple rows (single field) into string (2000)

    <font face="Georgia">- The DefectSummary field will start with ", "</font face=georgia>
    <img src=/S/yep.gif border=0 alt=yep width=15 height=15>Yes, I should have mentioned this when posting. It's easily overcome though, for example by using the RIGHT() function.

    <font face="Georgia">- It'll concatenate only the first two values.</font face=georgia>
    <img src=/S/nope.gif border=0 alt=nope width=15 height=15> When I tested it, all the values were concatenated successfully.

    <font face="Georgia">- You'd have to run the update query each time the Defects records have been edited.</font face=georgia>
    <img src=/S/yep.gif border=0 alt=yep width=15 height=15>Very true, and in fact the query would cause the summary field to contain duplicate values (unless it is cleared before running the query). I was under the impression that the report in question was a one-off, but that was probably overly presumptious of me.

    <font face="Georgia">- Storing calculated values is redundant.</font face=georgia>
    <img src=/S/yep.gif border=0 alt=yep width=15 height=15> I agree, although (again) my impression was that the report was a one off, so the summary field would be deleted after use. The approach I suggested requires the values to be stored in a table due to its iterative nature (i.e. each line of the update query adds the defect type to the relevant defect summary).

    Certainly, on balance, mine is a very quick-and-easy, rough-and-ready technique which would suffice for a one-off bit of data analysis but would be unsuitable for a permanent/ongoing report.
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: muliple rows (single field) into string (2000)

    I'll try both I think, not sure if its a 'one off'. No doubt the users will change their minds whatever i assume.


    Thanks <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  7. #7
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: muliple rows (single field) into string (2000)

    One caveat for the concat function: If you have a large amount of data and a slower computer/small amount of free memory, you may need to store the results in a temporary table. I use it for a mail merge into Word and my computer would crash on the merge until I started storing the values in a table.

    A small point, but it might save you some frustration in the testing phase.

Posting Permissions

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