1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Dynamic Concatenation--Access2003/?VB?

Discussion in 'Business Applications' started by WAJ0606, Apr 17, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. WAJ0606

    WAJ0606 Thread Starter

    Joined:
    Oct 10, 2007
    Messages:
    137
    Simple Example
    Column A Column B
    1 2
    1 3
    1 4

    Desired Result
    Column A Column B
    1 2,3,4

    The number of concatenated records will vary.
    I think this might require some VB and a loop unless there is a function I am unaware of.
    Any ideas?
    Note: posting threw my alignment off, but hopefully you get the idea...value 2,3 and 4 are supposed to be in column B
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Do you just want to display the data that way, rewrite the Table that way or Create a New Table with that data?
     
  3. WAJ0606

    WAJ0606 Thread Starter

    Joined:
    Oct 10, 2007
    Messages:
    137
    Create a new table/query the data that way.
    I came across this function http://www.tek-tips.com/faqs.cfm?fid=4233 after I posted, but cannot get it to compile with:
    SOs: Concatenate("SELECT dbo_SAP_FLEX_OB_WITH_COMMENTS.[SAP Sales Order Nbr] FROM dbo_SAP_FLEX_OB_WITH_COMMENTS
    WHERE dbo_SAP_FLEX_OB_WITH_COMMENTS.[Project ID] =""" & [Project ID] & """")

    Anything stick out?

    I know this isn't relevant, but it's always nice to know how the data is being used. I'm writing a report that groups at the project level and each project contains multiple sales orders.
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I am not that good with SQL, but if this is for a Module or VBA code then the Where statement won't work due to the """"", when you have multiple instances of "" you have to alternate them with the '
    However you might find that you do not need them at all for an ID NUmber, so you could try
    WHERE dbo_SAP_FLEX_OB_WITH_COMMENTS.[Project ID] =" & [Project ID])
    or
    WHERE dbo_SAP_FLEX_OB_WITH_COMMENTS.[Project ID] = '" & [Project ID] & '")

    The syntax of VBA SQL statements is an Absolute nightmare requiring much trial and error to get it to work.
    Whereas working with recordsets is more straightforward, although it will probably run slightly slower.
    It should be possible to do it with 2 simple queries though, the first query to append the First Instance of Column A to the new Table and the second query to update the Column B with the additional record's data.
    You will need to set the "Printing" table's Column A to Indexed with "No Duplicates".
     
  5. WAJ0606

    WAJ0606 Thread Starter

    Joined:
    Oct 10, 2007
    Messages:
    137
    The Project ID field is a little misleading, it contains text and integers, so its a string. This is a module inside of Access.
    I tried alternating "'.....
    Concatenate("SELECT dbo_SAP_FLEX_OB_WITH_COMMENTS.[SAP Sales Order Nbr] FROM dbo_SAP_FLEX_OB_WITH_COMMENTS
    WHERE dbo_SAP_FLEX_OB_WITH_COMMENTS.[Project ID]= "'" & dbo_SAP_FLEX_OB_WITH_COMMENTS.[Project ID] & "'"')

    That returned an invalid syntax error.

    As for doing it with two queries, I need the Sales order values in one record. When I export it to Excel the project ID should be in A1 and all the Sales Orders corresponding to that Project should be in A2. How would that be capable via queries?
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    As I explained in the last Post, Create an "Export" or "Printing" Table, set Column A to Indexed with No Duplicates.
    Create an Append Query that adds both Columns to the New table, it will only add the first Instance of the ID.
    Create an Update Query for the New Table, add the Original table and join them via Column A.
    You only need one column, Column B, in that column's "Update To" box enter
    Column B's Field Name & ", " & [Original Table Name].[Column B's Field Name]
    So what you do in essence, is add your Original Records to the first record with ", " between each value.
    You might need a 3rd query to "Delete" the data in the new Table if you need to reset the data completely between runs.
     
  7. WAJ0606

    WAJ0606 Thread Starter

    Joined:
    Oct 10, 2007
    Messages:
    137
    Thanks OBP, I think that has it.

    I wasn't familiar with the indexed property, so that threw me for a loop when you mentioned it. I did have to make one minor change. If I updated the table with Id's and SO numbers then the SO # that was already in via my append would be duplicated on the update. Instead I left the SO# unpopulated in the export table, which have me a leading ", " after the update. So i then ran a right(field,len(field)-2).

    Thanks again for all your help. If there is something I missed that makes the text edit function unnecessary let me know.
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/704698

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice